gplivna.eu

Alternative ways to activate trace

 

For my preffered ways to activate oracle trace look at Oracle trace description article. All below mentioned ways to enable/disable tracing are possible in 10g, but not all in previous versions. For example package dbms_monitor was not available in 9i.

 

Using alter session set sql_trace ...

 

alter session set sql_trace = true;

which in functionality is equivalent to the

alter session set events '10046 trace name context forever, level 1';

 

according disable trace statement is

alter session set sql_trace = false;

 

Using dbms_session.set_sql_trace procedure

 

Activate

dbms_session.set_sql_trace (true);

Deactivate

dbms_session.set_sql_trace (false);

 

According to documentation it is is equivalent to the

alter session set sql_trace = {true|false};

 

Using dbms_support.start_trace procedure

 

Activate

dbms_support.start_trace (binds=>{true|false}, waits=>{true|false});

Deactivate

dbms_support.stop_trace;

 

Dbms_support is not installed by default. For more info about dbms_support package see metalink Note:62294.1.

 

Using alter session set events ...

 

Described in my above mentioned article.

 

Using dbms_system.set_ev procedure

 

Described in my above mentioned article.

 

Using oradebug command

 

Available commands are described in metalink Note:29786.1

Examples of activating trace via oradebug are in metalink Note:376442.1

 

Using dbms_monitor.session_trace_enable procedure

 

Activate for your own session

dbms_monitor.session_trace_enable (binds=>{true|false}, waits=>{true|false});

Deactivate

dbms_monitor.session_trace_disable;

 

For activating trace in other sessions the same procedure is used, just arguments session_id and serial_num should be supplied.

 

Dbms_monitor has another valuable procedure pair:

 

serv_mod_act_trace_enable and serv_mod_act_trace_disable, which enables and disables trace for given service_name, module and action.

For example for a given service name you can trace all session started from SQL*Plus.

Module and action in your own created application can be set using dbms_application_info set_module and set_action procedures.

 

serv_mod_act_trace_enable fills sys table wri$_tracing_enabled and view dba_enabled_traces on top of this table as follows:

 

SQL> exec dbms_monitor.serv_mod_act_trace_enable(service_name=>'orcl', module_name=>'SQL*Plus')

 

PL/SQL procedure successfully completed.

 

SQL> select * from sys.wri$_tracing_enabled;

 

TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 INSTANCE_NAME FLAGS

---------- ---------- ------------- ------------- ------------- -----

˙˙˙˙˙˙˙˙ 4 orcl˙˙˙˙˙˙ SQL*Plus˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙ ˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙8

SQL> select * from dba_enabled_traces;

 

TRACE_TYPE ˙˙˙˙PRIMARY_ID QUALIFIER_ID1 QUALIFIER_ID2 WAITS BINDS INSTANCE_NAME

-------------- ---------- ------------- ------------- ----- ----- -------------

SERVICE_MODULE orcl˙˙˙˙˙˙ SQL*Plus ˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙TRUE FALSE˙˙˙˙˙˙˙˙˙˙˙˙˙

 

For other dbms_monitor procedures refer to documentation.

 

V$session columns sql_trace, sql_trace_waits, sql_trace_binds

 

dbms_system.set_ev, dbms_support.start_trace, dbms_monitor.session_trace_enable are procedures

that sets sql_trace, sql_trace_waits, sql_trace_binds in v$session.

For all other above explained methods these values are not set.

Also if you try to set these values for other sessions then v$session is updated only after the other session has made some activity and some info is written in trace file.

 

Trace file names

 

On various platforms and versions trace file names differ. Although the common feature is to have extension trc and one can use ALTER SESSION SET tracefile_identifier = 'blabla' to enforce the file name to contain given identifier at least for your own session.

 

First edition: 2007-06-17

Thanks to user Apex in OTN forums, (there isn't level 0 of course): 2007-09-28