How to Enable SQL_TRACE for Another Session or in MTS Using Oradebug

The ORADEBUG utility can enable/disable setting the SQL tracing for another 
user's session or an MTS session.  To enable tracing for another session, the 
Oracle process identifier (PID) or the Operating System processes identifier 
(SPID) must be identified from v$process.  This is an effective way of capturing 
a SQL trace from a process which is already running.  The output can be used to 
analyze SQL related performance issues. 
 
The ORADEBUG dump produces a trace file in the user_dump_dest that can be 
formatted with TKPROF.  The ORADEBUG is a utility which is available from 
Server Manager line mode (svrmgrl).  This utility is available in Oracle 
versions 7.3 and up. 
   
NOTE:  If using an older version of Oracle (prior to Oracle9), use 'svrmgrl'
instead of SQL*Plus as shown in the examples below.

e.g.
> svrmgrl
SVRMGR>  connect internal



Do the following:  
 
1. Obtain the Oracle process identifier or the Operating System process 
   identifier (SPID) from v$process in a non MTS environment: 

       >  sqlplus /nolog
       SQL> connect / as sysdba 
       SQL> select pid, spid, username from v$process; 
 
               PID    SPID   USERNAME 
               ----   -----  -------- 
               8      25807  oracle 

   Obtain the Oracle process identifier or the Operating System process 
   identifier (SPID) from v$process in an MTS environment:

          sqlplus /nolog
         SQL> connect / as sysdba
         SQL> select pid, spid from v$process p, v$shared_server s
              2  where p.addr = s.paddr;

       PID SPID
---------- ------------
        14 6976

    
 
2. Attach to the process using ORADEBUG. 
 
   Using the Oracle process identifier: 
 
       SQL> oradebug setorapid 8 
 
       Unix process pid: 25807, image: oracleV804 
 
 
   - or -  
  
 
   Using the Operating System process identifier: 
 
       SQL> oradebug setospid 25807 
 
       Oracle pid: 8, Unix process pid: 25807, image: oracleV804 
  
 
3. Turn on SQL Trace for the session. 
 
       SQL> oradebug event 10046 trace name context forever, level 12 
 
       Statement processed. 
 
 4. Get the name and location of the tracefile generated
 
       SQL> oradebug tracefile_name
         /opt/oracle/admin/db92/udump/db92_ora_16921.trc

5. Turn off the SQL trace for the session.  
 
 
       SQL> oradebug event 10046 trace name context off 
 
 
6. Format trace file using TKPROF.