Trace Files -- 2 : Generating SQL Traces (another session)

Here are a few methods to trace another session to capture SQL statement executions.  All of these methods require the appropriate privilege --- which most DBAs seem to mean using SYS (which logs in AS SYSDBA).  I leave it to you to discover the privilege -- save to say that you do NOT need to login AS SYSDBA.


DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE
This enables tracing for sessions of a specific Service_Name and *optionally* Module Name and Action Name.  This is useful where you define applications by Service Names and, optionally, use DBMS_APPLICATION_INFO to set Module and Action in a given session.

Thus, if in an HR session, I do :

Enter user-name: hr/hr@orcl

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exec dbms_application_info.set_client_info('HR App Client');

PL/SQL procedure successfully completed.

SQL> exec dbms_application_info.set_module('Employee Module','Updating');

PL/SQL procedure successfully completed.

SQL> 

And, in another session (with the appropriate privileges), I do :

SQL> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(-                                      
> service_name=>'orcl',-
> module_name=>'Employee Module',-
> action_name=>'Updating');

PL/SQL procedure successfully completed.

SQL> 

the actions in the HR session(s) of that module and action are traced.  (Additional parameters WAITS and BINDS can also be set to TRUE to enable of Waits and Binds (Waits are set to TRUE by default)).
Note : If there are multiple sessions with the same combination of service_name, module_name, action_name, all the sessions are traced !

Tracing is disabled when the session itself uses DBMS_APPLICATION_INFO to change it's Module / Action settings.

Tracing is also disabled when the session that initiated the tracing executes :

SQL> exec DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(-
> service_name=>'orcl',-
> module_name=>'Employee Module',-
> action_name=>'Updating');

PL/SQL procedure successfully completed.

SQL> 

Thus, this procedure allows tracing by the granularity of Service = Module = Action.  Unfortunately, many custom applications do NOT use DBMS_APPLICATION_INFO to set Module and Action.


DBMS_MONITOR.SESSION_TRACE_ENABLE
This is useful for tracing a single session and where  Module / Action information are not populated by the client.

The call is simple :

SQL> exec DBMS_MONITOR.SESSION_TRACE_ENABLE(-
> session_id=>153,-
> serial_num=>33,-
> waits=>TRUE,-
> binds=>TRUE);

PL/SQL procedure successfully completed.

SQL> 

The disabling call is :

SQL> exec DBMS_MONITOR.SESSION_TRACE_DISABLE(-
> session_id=>153,-
> serial_num=>33);

PL/SQL procedure successfully completed.

SQL> 
>

Thus, this can be issued individually for each session.

.
.
.