How to trace an Oracle session from another session

Posted by David Yahalom under Oracle

DBMS_MONITOR is a package which allows tracing of session activity.
Using DBMS_MONITOR you can start a trace of any session in the database – from whatever session you are connected to. It doesn’t even have to be the session you want to trace.

So, for example, if you have session A running and you are connected from session B, you can trace session A to diagnose its activity from session B using the DBMS_MONITOR package.

DBMS_MONITOR requests the database S.P to generate the trace files required. These trace files contain information about queries and query stats including wait events and even bind variable information.

The syntax is:

DBMS_MONITOR.CLIENT_ID_TRACE_ENABLE(client_id, wait info, bind variables);

For example:

exec DBMS_MONITOR.SESSION_TRACE_ENABLE (40,105,true,true);

This will start a trace for a specific session identified with sid value of 40 and serial# value of 105 and will generate trace files which include wait events and bind variable information.

Stopping the trace once the information is collected is simple and ca be done by running:

exec DBMS_MONITOR.SESSION_TRACE_DISABLE(40,105);

Following the creation of the raw dump files, we can use the tkprof  utility to format the trace file output. Tracing a session can generate very large trace files if left in its enabled state or for a session doing a lot of activity – this can fill the user dump directory pretty fast.