How to Enable SQL_TRACE for Another Session Using ORADEBUG:
===========================================================
 
The ORADEBUG utility can enable/disable setting the SQL tracing for another 
user's 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. 
   
  
Do the following:  
 
1. Obtain the Oracle process identifier or the Operating System process 
   identifier (SPID) from v$process: 
 
       SVRMGR> select pid, spid, username from v$process; 
 
               PID    SPID   USERNAME 
               ----   -----  -------- 
               8      25807  oracle 
    
 
2. Attach to the process using ORADEBUG. 
 
   Using the Oracle process identifier: 
 
       SVRMGR> oradebug setorapid 8 
 
       Unix process pid: 25807, image: oracleV804 
 
 
   - or -  
  
 
   Using the Operating System process identifier: 
 
       SVRMGR> oradebug setospid 25807 
 
       Oracle pid: 8, Unix process pid: 25807, image: oracleV804 
  
 
3. Turn on SQL Trace for the session. 
 
       SVRMGR> oradebug event 10046 trace name context forever, level 12 
 
       Statement processed. 
 
 
4. Turn off the SQL trace for the session.  
 
 
       SVRMGR> oradebug event 10046 trace name context off 
 
 
5. Format trace file using TKPROF.