2011-06-14

ORACLE TRACE

Remember that trace files are in the user_dump_destination, but for jobs and for shared server configurations they are in background_dump_destination.
Oracle 9i

turn on trace in another session:

EXECUTE dbms_system.set_ev ([sid],[serial#],10046,[level],'');
turn off
EXECUTE dbms_system.set_ev ([sid],[serial#],10046,0,'');

Oracle 9i/10g

turn on trace for current session:

ALTER SESSION SET EVENTS '10046 trace name context forever, level [level#]'
  • level 0 #off
  • level 1 #default
  • level 4 #default + bind variable values
  • level 8 #default + wait event information
  • level 12 #level 4 + level 8
turn off
ALTER SESSION SET EVENTS '10046 trace name context off'

Oracle 9i/10g

turn on trace for os process:

ORADEBUG SETOSPID [os process from v$process];
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL [level#];
ORADEBUG TRACEFILE_NAME; --display current tracefile
turn off
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

Oracle 9i/10g

turn on trace for current session:

dbms_support package in $ORACLE_HOME/rdbms/admin/dbmssupp.sql
EXEC dbms_support.start_trace(waits=>TRUE, binds=>TRUE);
turn off
EXEC dbms_support.stop_trace;

turn on trace for the other session:

EXEC dbms_support.start_trace_in_session(sid=>[sid], serial=>[serial#], waits=>TRUE, binds=>TRUE);
turn off
EXEC dbms_support.stop_trace_in_session(sid=>[sid], serial=>[serial#]);

Oracle 10g

turn on trace for current session:

exec DBMS_MONITOR.SESSION_TRACE_ENABLE (session_id => [sid],serial_num => [serial#], waits => TRUE,binds => TRUE);
turn off
exec DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id=> [sid],serial_num=> [serial#]);
check:
SELECT sql_trace,sql_trace_waits,sql_trace_binds FROM v$session;

turn on trace for client:

exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id => '[client_name]',waits => TRUE, binds => TRUE);
turn off
exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => '[client_name]');

turn on trace at database level:

exec DBMS_MONITOR.DATABASE_TRACE_ENABLE (waits => TRUE,binds => TRUE,instance_name > NULL);
turn off
exec DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name > NULL);
check:
SELECT * FROM dba_enabled_traces;

Oracle 11g

turn on trace at component level

exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => '[serv_name]', module_name => '[module]',action_name => '[action]',waits => TRUE,binds => FALSE, instance_name => NULL);
turn off
exec DBMS_MONITOR.SERV_MOD_TRACE_DISABLE(service_name => 'serv_name',module_name => 'module',action_name => '[action]',instance_name => NULL);
check: SELECT * FROM dba_enabled_traces;