Session Level Tracing

Overview

The following details how to collect diagnostic trace information for issues at the system and session level.

Commands (high level)

 alter session set tracefile_identifier='10046'; 
 alter session set timed_statistics = true; 
 alter session set statistics_level=all; 
 alter session set max_dump_file_size = unlimited; 
 alter session set events '10046 trace name context forever,level 12'; 

 -- SQL to be Traced Here 
 SELECT * FROM dual;

 exit; 

Tracing Session

 -- Start Tracing
 ALTER SESSION SET tracefile_identifier = 'TRACE101';

 BEGIN
    dbms_session.session_trace_enable(
            waits => TRUE,
            binds => TRUE
    );
 END;

 --Run SQL or Process Here
 <<<SQL>>>

 -- Stop Tracing
  BEGIN
    dbms_session.session_trace_disable();
  END;

 -- Format Trace File Using tkprof (makes it readable)
 tkprof /u01/app/oracle/diag/rdbms/mydbname/mydbname/trace/mydbname_ora_3572083_TRACE101.trc 
        session_report.txt sys=no waits=yes sort=exeela,fchela
 Place above on one line.