Forum Stats

  • 3,769,601 Users
  • 2,252,990 Discussions
  • 7,875,117 Comments

Discussions

Tracefile not getting created

Apoorva S-Oracle
Apoorva S-Oracle Member Posts: 53 Employee
edited Jan 24, 2018 3:26AM in General Database Discussions

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

Ian BaugaardAJApoorva S-Oracle

Best Answer

  • AJ
    AJ Member Posts: 1,343 Bronze Trophy
    edited Jan 23, 2018 4:50AM Accepted Answer

    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

    Ian BaugaardApoorva S-Oracle

Answers

  • AJ
    AJ Member Posts: 1,343 Bronze Trophy
    edited Jan 23, 2018 4:50AM Accepted Answer

    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

    Ian BaugaardApoorva S-Oracle
  • Apoorva S-Oracle
    Apoorva S-Oracle Member Posts: 53 Employee
    edited Jan 23, 2018 5:29AM

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

    Snap33.png

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

  • AJ
    AJ Member Posts: 1,343 Bronze Trophy
    edited Jan 23, 2018 5:38AM

    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
    Apoorva S-Oracle Member Posts: 53 Employee
    edited Jan 23, 2018 6:03AM

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

  • AJ
    AJ Member Posts: 1,343 Bronze Trophy
    edited Jan 23, 2018 6:18AM

    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

    Apoorva S-Oracle
  • Selvakumar.Nagulan
    Selvakumar.Nagulan Member Posts: 410
    edited Jan 23, 2018 6:32AM

    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!

    Apoorva S-OracleApoorva S-Oracle
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jan 23, 2018 7:21AM
    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
    2. select
    3. b.sid,
    4. b.serial#,
    5. a.spidprocessid
    6. fromv$processajoinv$sessionb
    7. on(a.addr=b.paddr)
    8. 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

    AJApoorva S-Oracle
  • AJ
    AJ Member Posts: 1,343 Bronze Trophy
    edited Jan 23, 2018 7:27AM

    Nice, I didn't know that. Thanks.

    AJ

  • Apoorva S-Oracle
    Apoorva S-Oracle Member Posts: 53 Employee
    edited Jan 24, 2018 3:09AM

    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
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Jan 24, 2018 3:26AM
    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.

This discussion has been closed.