Tracing ...

TRACING SESSIONS ...

Things you will want to know about tracing

* The timed_statistics init.ora parameter must be set to true in
order to get any meaningful timing data out of tkprof.

* I prefer to set the max_dump_file_size init.ora parameter to either
unlimited or a really large value (2G).
If you're going through the extra work of tracing a process you don't want to
potentially miss the most important part.

-- set identifier in output file
SQL> alter session set tracefile_identifier='mytest';

-- Turn on session level tracing
SQL> alter session set sql_trace=true;

-- Turn on TRACING IN YOUR CURRENT SESSION.
EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);

-- Turn on some trace level.
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

-- SOME DBA STUFF
-- trace another session
EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);

-- trace another session with some trace level
EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');