Trace a session in Oracle

STEP-1 : Get SID, SERIAL# that you wanted to trace :

select username, status, sid, serial# from v$session
where username ='SCHEMA_NAME' order by 1;

SQL> select username, status, sid, serial# from v$session
where username ='PRODSUPT' order by 1;
USERNAME STATUS SID SERIAL#
-------- ------ --- -------
PRODSUPT ACTIVE 3   5023

STEP-2 :

DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(SID, SERIAL#, TRUE);

exec dbms_system.set_sql_trace_in_session(3,5023,true);
exec dbms_system.set_sql_trace_in_session(3,5023,false);

STEP-3: package needs to be enable:

DBMS_SUPPORT.START_TRACE_IN_SESSION(SID, SERIAL#, WAITS, BINDS);

SQL> @?/rdbms/admin/dbmssupp.sql
Package created. Package body created.
exec dbms_support.start_trace_in_session(3,5025,true,true);
exec dbms_support.stop_trace_in_session(3,5025);

STEP-4: 

DBMS_MONITOR.SESSION_TRACE_ENABLE(SESSION_ID, SERIAL_NUM,BINDS,WAITS);

SQL> exec dbms_monitor.session_trace_enable(session_id=>3,serial_num=>5027,binds=>true,waits=>true);
PL/SQL procedure successfully completed.
SQL> exec dbms_monitor.session_trace_disable(session_id=>3,serial_num=>5027);
PL/SQL procedure successfully completed.

STEP-5:

After you’ve done your trace, well – and while you’re doing it, you should be able to find a trace file in your trace directory:

->ll -rt *.trc
-rw-r----- 1 oracle dba 1057 May 24 10:35 T10AC_arc3_9069.trc
-rw-r----- 1 oracle dba 977 May 24 10:39 T10AC_lgwr_8922.trc
-rw-r----- 1 oracle dba 2127 May 24 10:49 T10AC_ora_3029.trc
-rw-r----- 1 oracle dba 65169 May 24 10:50 T10AC_ora_4412.trc

STEP-6:

TKPROF

You can now use tkprof to format you trace file into a more readable format. The basic syntax is:

tkprof <source trace file> <output file>
tkprof T10AC_ora_4412.trc T10AC_ora_4412.tkprofs

 STEP-7:

Important: TRACING AND MAX_DUMP_FILE_SIZE

While tracing a poor query. The database had max_dump_file_size set to prevent overwhelming the system with huge trace files. It will always be safer to increase the size to unlimited to so I could get my trace to complete. When you do this

alter session set max_dump_file_size = unlimited;