Tracefile not getting created

Apoorva S-Oracle

Hi,

I have enabled SQL trace for orclpdb and I have the following code in a SQL script:

set echo onconnect trace/[email protected] session set tracefile_identifier='mytraceP3';update sales set amount_sold=20000 where prod_id=13 and cust_id=987;commit;create index sales_prod_cust_indx on sales(prod_id,cust_id);connect trace/[email protected] session set tracefile_identifier='mytraceS3';update sales set amount_sold=30000 where prod_id=13 and cust_id=987;commit;connect trace/tracedrop index sales_prod_cust_indx;exit;

The tracefile for mytraceP3 gets generated but mytraceS3 does not get generated.

Could somebody please help me?

- Apoorva

AJ

You have only set a tracefile identifier, but you have not enabled the SQL trace.

For example:

alter session set tracefile_identifier='mytrace';EXEC DBMS_MONITOR.session_trace_enable;

AJ

AJ

You have only set a tracefile identifier, but you have not enabled the SQL trace.

For example:

alter session set tracefile_identifier='mytrace';EXEC DBMS_MONITOR.session_trace_enable;

AJ

Apoorva S-Oracle

I have already enabled it in the Enterprise Manager (EMCC).

My issue is that mytraceP3 is getting generated but not mytraceS3.

AJ

Okey. Are you sure that the session you cannot see are connected through that service, then?

I'd double check that.

AJ

Apoorva S-Oracle

Yes, I am sure because the first trace file is getting generated.

AJ

Okey, that's strange if that is the case. Have you checked if you get a trace file at all (one with just the PID identifier) for the session you are tracing?

-- extract the PIDselect  b.sid,  b.serial#, a.spid processidfrom v$process a join v$session b on ( a.addr = b.paddr ) and b.audsid = userenv('sessionid');

and look for a file with <sid>_ora_<pid>.trc in you trace directory.

AJ

Selvakumar.Nagulan

Hi,

A session is identified by the sid. When you connect to a user again, the SID changes ending the previously turned trace off.

SQL> conn c##ggadmin/[email protected]> select sys_context('USERENV','SID') from dual;SYS_CONTEXT('USERENV','SID')--------------------------------------------------------------------------------3809SQL>  conn c##ggadmin/[email protected]> select sys_context('USERENV','SID') from dual;SYS_CONTEXT('USERENV','SID')--------------------------------------------------------------------------------1563SQL> conn c##ggadmin/[email protected]> select sys_context('USERENV','SID') from dual;SYS_CONTEXT('USERENV','SID')--------------------------------------------------------------------------------3772SQL>

You have to turn on tracing again after connecting.

Thanks!

AndrewSayer

AJ wrote:Okey, that's strange if that is the case. Have you checked if you get a trace file at all (one with just the PID identifier) for the session you are tracing?

1. --extractthePID
4. select
5. b.sid,
6. b.serial#,
7. a.spidprocessid
8. fromv$processajoinv$sessionb
9. on(a.addr=b.paddr)
10. andb.audsid=userenv('sessionid');

-- extract the PID   select   b.sid,   b.serial#,  a.spid processid from v$process a join v$session b  on ( a.addr = b.paddr )  and b.audsid = userenv('sessionid'); 

and look for a file with <sid>_ora_<pid>.trc in you trace directory.AJ

v$process has TRACEFILE since 11.1 which takes out the need derive it yourself

AJ

Nice, I didn't know that. Thanks.

AJ

Apoorva S-Oracle

Thank you for all the help!

Even though I had enabled trace on the EMCC, I had to add the following statement after the CONNECT statement:

EXEC DBMS_MONITOR.session_trace_enable;

It worked!

AndrewSayer

Apoorva S-Oracle wrote:Thank you for all the help!Even though I had enabled trace on the EMCC, I had to add the following statement after the CONNECT statement:EXEC DBMS_MONITOR.session_trace_enable;
It worked!

I would guess that the EMCC process is only going through the connected sessions and enabling trace at the time you hit the button.

There are other procedures that will Enable tracing for new sessions depending on their service/module/action, in which case the newly connected session (so long as it uses the service you’ve chosen) will be automatically traced. This could be particularly dangerous if you forget you’ve ran the procedure or didn’t know it would work on all future connections.