Here are some possible ways to setup Trace:
To Enable trace at instance level
1) Set the parameter sql_trace in the pfile or spfile. It will enable trace for all sessions and the background processes
sql_trace = TRUE to enable it or to disable trace use sql_trace = FALSE
2)To enable tracing without restarting the databasejust run the following command using sqlplus:
SQL> ALTER SYSTEM SET trace_enabled = TRUE; To start trace
SQL> ALTER SYSTEM SET trace_enabled = FALSE; To stop trace
To enable trace at session level
If need generate trace at your own sesion use:
SQL>ALTER SESSION SET sql_trace = TRUE; to strat the trace
SQL>ALTER SESSION SET sql_trace = FALSE; to stop the trace
or
SQL>EXECUTE dbms_session.set_sql_trace (TRUE); to start trace
SQL>EXECUTE dbms_session.set_sql_trace (FALSE); to stop trace
or
SQL>EXECUTE dbms_support.start_trace; to start trace
SQL>EXECUTE dbms_support.stop_trace; to stop trace
To Enable trace in a different session
Find out the SID and SERIAL# from v$session using:
SQL>SELECT * FROM v$session WHERE osuser = OSUSER;
and use:
SQL>EXECUTE dbms_support.start_trace_in_session (SID, SERIAL#); to start trace
SQL>EXECUTE dbms_support.stop_trace_in_session (SID, SERIAL#); to stop trace
or
SQL>EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, TRUE); to start trace
SQL>EXECUTE dbms_system.set_sql_trace_in_session (SID, SERIAL#, FALSE); to stop trace
On 10g you can also use DBMS_MONITOR:
BEGIN
->Enable/Disable Client Identifier Trace.
DBMS_MONITOR.client_id_trace_enable (client_id => ‘my_id’);
DBMS_MONITOR.client_id_trace_disable (client_id => ‘my_id’);
->Enable/Disable Service, Module and Action Trace.
DBMS_MONITOR.serv_mod_act_trace_enable ( service_name => ‘my_service’);
DBMS_MONITOR.serv_mod_act_trace_enable ( service_name => ‘my_service’, module_name => ‘my_module’);
DBMS_MONITOR.serv_mod_act_trace_enable ( service_name => ‘my_service’, module_name => ‘my_module’, action_name => ‘INSERT’);
DBMS_MONITOR.serv_mod_act_trace_disable ( service_name => ‘my_service’, module_name => ‘my_module’, action_name => ‘INSERT’);
->Enable/Disable Session Trace .
DBMS_MONITOR.session_trace_enable;
DBMS_MONITOR.session_trace_enable ( session_id => 12, serial_num => 1011);
DBMS_MONITOR.session_trace_disable ( session_id => 12, serial_num => 1011);
END;
/
For more information regarding DBMS_MONITOR, please refer to the following link:
Also take a look on this link:
Cheers,Francisco Munoz Alvarez
Hi Francisco
I take my traces with
dbms_system.set_ev(sid,serial#,,trace_level,….
Do you think this is the better way to take a trace?
Mauricio
Hi Mauricio,
The DBMS_System package contains a number of routines that can be useful on occasion. Oracle clearly state that these routines are not supported so proceed at your own risk.
*WARNING* Do not use an Oracle Diagnostic Event unless directed to do so by Oracle Support Services or via a Support related article on Metalink.
Incorrect usage can result in disruptions to the database services.
Set_Ev is used to set trace on for a specific event:
Dbms_system.set_ev (
si binary_integer, — SID
se binary_integer, — Serial#
ev binary_integer, — Event code or number to set.
le binary_integer, — Usually level to trace
cm binary_integer — When to trigger (NULL = context forever.)
Example:
EXEC DBMS_System.Set_Ev(sid, serial#, event, level, name);
EXEC DBMS_System.Set_Ev(62, 17, 10046, 4, ”);
Where level indicates the following levels of trace:
0 – Disable Trace
1 – Standard SQL_TRACE functionality.
4 – As level 1 plus tracing of bind variables.
8 – As level 1 plus wait events.
12 – As level 1 plus bind variables and wait events.
To disable 10046 tracing enter the following:
Exec dbms_system.set_ev(62,17,10046,0,’’);
To disable tracing for another user’s session use:
DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,false);
For more information refer to Metalink Note:
– Introduction to ORACLE Diagnostic EVENTS – NOTE:218105.1
Cheers,
Francisco Munoz Alvarez