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 off
EXECUTE dbms_system.set_ev ([sid],[serial#],10046,0,'');
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
ALTER SESSION SET EVENTS '10046 trace name context off'
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
ALTER SESSION SET EVENTS '10046 trace name context off'
Oracle 9i/10g
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL [level#];
ORADEBUG TRACEFILE_NAME; --display current tracefile
turn off
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;
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
EXEC dbms_support.start_trace(waits=>TRUE, binds=>TRUE);
turn off
EXEC dbms_support.stop_trace;
turn off
EXEC dbms_support.stop_trace_in_session(sid=>[sid], serial=>[serial#]);
turn on trace for current session:
dbms_support package in $ORACLE_HOME/rdbms/admin/dbmssupp.sqlEXEC 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 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 off
exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE(client_id => '[client_name]');
turn off
exec DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name > NULL);
check:
SELECT * FROM dba_enabled_traces;
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 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;
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;