Oracle trace
转自 http://www.juliandyke.com/Diagnostics/Trace/EnablingTrace.html
There are several ways to enable trace
Enabling Trace at Instance Level
Trace can be enabled when the instance is started by adding the following line to the init.ora file
sql_trace = TRUE
This will enable trace for all sessions including background processes. Note that enabling this parameter may generate large amounts of trace and consume significant system resources
Trace can also be enabled at instance level using event 10046. For example to enable event 10046 level 8 add the following line to the init.ora file
event="10046 trace name context forever, level 8"
When the instance is already running, trace can be enabled directly using the ALTER SYSTEM command.
ALTER SYSTEM SET trace_enabled = TRUE;
This will enable trace for all newly created sessions. Currently executing sessions and background processes will be unaffected
Instance-wide trace can be disabled again using
ALTER SYSTEM SET trace_enabled = FALSE;
Trace can also be enabled using event 10046. For example to enable event 10046 level 8 use the command
ALTER SYSTEM SET EVENTS '10046 trace name context forever, level 8';
Instance-wide trace can be disabled again using
ALTER SYSTEM SET EVENTS '10046 trace name context off';
Enabling Trace at Session Level
Trace can be enabled at session level using the command
ALTER SESSION SET sql_trace = TRUE;
Trace is disabled at session level using
ALTER SESSION SET sql_trace = FALSE;
Trace can also be enabled at session level using event 10046 For example to enable event 10046 level 8 use the command
ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';
Event 10046 trace is disabled at session level using
ALTER SESSION SET EVENTS '10046 trace name context off';
Trace can be enabled in the current session using the DBMS_SESSION package. This can be useful if you need to enable trace from within a PL/SQL package.
Trace is enabled at session level using
EXECUTE dbms_session.set_sql_trace (TRUE);
Trace is disabled at session level using
EXECUTE dbms_session.set_sql_trace (FALSE);
Trace can be enabled in the current session using the DBMS_SUPPORT package. This provides more flexibility than DBMS_SESSION.
Trace is enabled at session level using
EXECUTE dbms_support.start_trace;
With no parameters, this procedure enables level 1 trace
Event 10046 level 4 trace can be enabled using
EXECUTE dbms_support.start_trace (binds=>true);
Event 10046 level 8 trace can be enabled using
EXECUTE dbms_support.start_trace (waits=>true);
Event 10046 level 12 trace can be enabled using
EXECUTE dbms_support.start_trace (binds=>true,waits=>true);
Trace can be disabled using
EXECUTE dbms_support.stop_trace;
Enabling Trace in another Session
Trace can be enabled in another session using the DBMS_SUPPORT package
The SID and optionally the serial number if the target session must be obtained from V$SESSION. The serial number can optionally be specified as 0.
For example to enable level 1 trace in a session with SID 9 and serial number 29 use
EXECUTE dbms_support.start_trace_in_session (9,29);
With no parameters, this procedure enables level 1 trace
Event 10046 level 4 trace can be enabled using
EXECUTE dbms_support.start_trace_in_session (9,29,binds=>true);
Event 10046 level 8 trace can be enabled using
EXECUTE dbms_support.start_trace_in_session (9,29,waits=>true);
Event 10046 level 12 trace can be enabled using
EXECUTE dbms_support.start_trace_in_session (9,29,binds=>true,waits=>true);
Trace can be disabled using
dbms_support.stop_trace_in_session (9,29);
Trace can be also be enabled in another session using the DBMS_SYSTEM package
The SID and the serial number of the target session must be obtained from V$SESSION. In this case the serial number must be specified
For example to enable trace in a session with SID 9 and serial number 29 use
EXECUTE dbms_system.set_sql_trace_in_session (9,29,TRUE);
Note this is equivalent to enabling event 10046 level 1
To disable trace in the same session use
EXECUTE dbms_system.set_sql_trace_in_session (9,29,FALSE);
Event 10046 trace can also be enabled in another session using the DBMS_SYSTEM package
The SID and the serial number of the target session must be obtained from V$SESSION.
For example to enable event 10046 level 8 in a session with SID 9 and serial number 29 use
EXECUTE dbms_system.set_ev (9,29,10046,8,'');
To disable event 10046 in the same session use
EXECUTE dbms_system.set_ev (9,29,10046,0,'');
Enabling Trace in Oracle5
This is probably only of historic interest today, but SQL trace was included in Oracle5. Thanks to Martin Berg and Mogens Norgaard for the following information
Trace can be enabled in Oracle5 using the command
SELECT TRACE ('trace_name',1),1 FROM dual;
To disable trace in Oracle5 use the command
SELECT TRACE ('trace_name',1),0 FROM dual;
where trace_name is one of the following
|
For example to enable SQL trace in Oracle5 use the command
SELECT TRACE ('sql',1),1 FROM dual;
To disable SQL trace in Oracle5 use the command
SELECT TRACE ('sql',1),0 FROM dual;
Enabling Trace in Oracle6
In Oracle6, trace can be enabled at instance level using the sql_trace init.ora parameter
Trace can be enabled at session level using
ALTER SESSION SET SQL_TRACE TRUE
Trace can be disabled at session level using
ALTER SESSION SET SQL_TRACE FALSE
Note that the syntax is different in Oracle6 from Oracle7
Note also that this information has been taken from the Oracle6 manuals and has not yet been verified on an Oracle6 database
