PT - Extracting Session level trace based on sid and serial#

Session level trace example  based on sid and serial#

• Checking the database name 

SQL> @d

NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN

--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------

TC12DEV   TC12DEV          TC12DEV                        READ WRITE           PRIMARY          NO                 0

• Command to check sessions details  (describing only particular session only for trace generation)

SQL> @sessions

     INST   SID SERIAL# USERNAME     OSUSER           PROGRAM    LOCKED S hh:mm:ss SQL_ID            SEQ# Current/LastEvent         State (sec)

--------- ----- ------- ------------ ---------------- ---------- ------ - -------- --------------- ------ ------------------------- --------------

MODULE                                                                                               ACTION

---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------

        1   298   33658 INFODBA      varunyadav       SQL Develo        I 00:29:48 cr6axzpwa6byz       62 SQL*Net message from clie WAITING   1788

SQL Developer

 9 rows selected.

• Extracting trace based on sis and session#

SQL> DEFINE v_sid =298

SQL> DEFINE v_serial =33658

SQL> BEGIN

DBMS_MONITOR.SESSION_TRACE_ENABLE( SESSION_ID => &v_sid, SERIAL_NUM =>

&v_serial, WAITS => TRUE, BINDS => FALSE);

END;

/  2    3    4    5

old   2: DBMS_MONITOR.SESSION_TRACE_ENABLE( SESSION_ID => &v_sid, SERIAL_NUM =>

new   2: DBMS_MONITOR.SESSION_TRACE_ENABLE( SESSION_ID => 298, SERIAL_NUM =>

old   3: &v_serial, WAITS => TRUE, BINDS => FALSE);

new   3: 33658, WAITS => TRUE, BINDS => FALSE);

PL/SQL procedure successfully completed.

SQL> SELECT P.TRACEFILE FROM V$SESSION S JOIN V$PROCESS P ON S.PADDR = P.ADDR WHERE S.SID = &V_SID;

old   1: SELECT P.TRACEFILE FROM V$SESSION S JOIN V$PROCESS P ON S.PADDR = P.ADDR WHERE S.SID = &V_SID

new   1: SELECT P.TRACEFILE FROM V$SESSION S JOIN V$PROCESS P ON S.PADDR = P.ADDR WHERE S.SID = 298

TRACEFILE

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/tc12dev/TC12DEV/trace/TC12DEV_ora_20976.trc

SQL> BEGIN

DBMS_MONITOR.SESSION_TRACE_DISABLE( SESSION_ID => &v_sid, SERIAL_NUM =>

&v_serial);

END;

/  2    3    4    5

old   2: DBMS_MONITOR.SESSION_TRACE_DISABLE( SESSION_ID => &v_sid, SERIAL_NUM =>

new   2: DBMS_MONITOR.SESSION_TRACE_DISABLE( SESSION_ID => 298, SERIAL_NUM =>

old   3: &v_serial);

new   3: 33658);

PL/SQL procedure successfully completed.

SQL>

Trace output: