To trace or not to trace

Oracle Trace reference …

g-se-tr.sql

Script below enables trace in another user session. It will list all three possible ways to achive this, through a) SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION, b) SYS.DBMS_SYSTEM.SET_EV and c) ORADEBUG. …

col set_trace for a136 heading "ENABLE TRACE IN ANY SESSION"


select
 'EXEC sys.dbms_system.set_bool_param_in_session -
 ( sid => ' || s.sid || ', serial# => ' || s.serial# || ', parnam => ''timed_statistics'', bval => true);' 
 || chr(10) ||
 'EXEC sys.dbms_system.set_int_param_in_session - 
 ( sid => ' || s.sid || ', serial# => ' || s.serial# || ', parnam => ''max_dump_file_size'', intval  => 2147483647);' 
 || chr(10) || chr(10) ||
 'EXEC sys.dbms_support.start_trace_in_session - 
 ( sid => ' || s.sid || ', serial => ' || s.serial# || ', waits => true, binds => true);' 
 || chr(10) ||
 'EXEC sys.dbms_support.stop_trace_in_session - 
 ( sid => ' || s.sid || ', serial => ' || s.serial# || ');' 
 || chr(10) || chr(10) ||
 'EXEC sys.dbms_system.set_sql_trace_in_session - 
 ( sid => ' || s.sid || ', serial# => ' || s.serial# || ', sql_trace => TRUE); -- !! No waits and bind data'
 || chr(10) ||
 'EXEC sys.dbms_system.set_ev - 
 ( si => ' || s.sid || ', se => ' || s.serial# || ', ev => 10046, LE => 12, nm => '''');'
 || chr(10) || chr(10) ||
 'ORADEBUG SETORAPID ' || p.pid || ' | ORADEBUG SETOSPID ' || p.spid
 || chr(10) ||
 'ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12' set_trace
  from v$session s, v$process p
Read more →