How can I start a trace?

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


SQL>EXECUTE dbms_session.set_sql_trace (TRUE); to start trace
SQL>EXECUTE dbms_session.set_sql_trace (FALSE); to stop trace


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


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:


->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 (    session_id => 12,    serial_num => 1011);  

DBMS_MONITOR.session_trace_disable (    session_id => 12,    serial_num => 1011);



For more information regarding DBMS_MONITOR, please refer to the following link:

Also take a look on this link:

Francisco Munoz Alvarez

2 thoughts on “How can I start a trace?

  1. Hi Francisco

    I take my traces with


    Do you think this is the better way to take a trace?


  2. 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.)


    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:


    For more information refer to Metalink Note:

    - Introduction to ORACLE Diagnostic EVENTS – NOTE:218105.1


    Francisco Munoz Alvarez

Leave a Reply