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


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

Advertisements

Oracle ACE Director and President of LAOUC, NZOUG and CLOUG. Organizer of LA and APAC OTN Tours,

Posted in Oracle FAQ, Questions
2 comments on “How can I start a trace?
  1. mauric says:

    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

  2. admin says:

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: