DBMS_MONITOR is a new package in ORACLE 10G. This package allows us, amongst others, to trace sessions in the database.

In order to trace a session we need information about the session. This information can be client identifier (set by the application) or sid and serial# of the session.

DBMS_MONITOR causes the server process to generate a trace file as it does using other trace mechanisms (such as SQL_TRACE parameter set to true, event 10046, etc.). Along with the trace information about queries and queries statistics, the trace can also have information about wait events and bind variables value.

Syntax:

 DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id,waits,binds);
 DBMS_MONITOR.SESSION_TRACE_ENABLE(SID,SERIAL#, waits, binds);
 

If not specifies, the trace file will include wait event information and will not include bind variables values.

Example #1:

 exec DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE('hr');
 

will start a trace for all session with client identifier ‘hr’.

To stop the trace we can use:

 exec DBMS_MONITOR.CLIENT_ID_TRACE_DISABLE('hr');
 

Example #2:

 exec DBMS_MONITOR.SESSION_TRACE_ENABLE(50,100,true,true);
 

Will start a trace on a specific session (sid=50 and serial#=100) and will include both bind variables value and wait events in the trace file.

To stop the trace we can use:

 exec DBMS_MONITOR.SESSION_TRACE_DISABLE(50,100);
 

After generating the trace we can execute tkprof to format the trace file.

Note: bind variables value will not appear in the formatted trace file, but only in the raw trace file.