How do I switch on sql_trace in another session that is already running ?

The Oracle (tm) Users' Co-Operative FAQ

How do I switch on sql_trace in another session that is already running ?

The SQL*Plus trace feature can be turned on for a running session if the SID and SERIAL# are known. This information is available from the v$session dynamic performance table (view). There are several different practical methods of querying v$session such as by the Oracle user id (username), by the OS user id (osuser), or by the OS process id (process) being the most common.

  column sid      format 999999
  column serial#  format 999999  
  column username format a12
  column osuser   format a12
  select  s.username, s.status,  s.sid,     s.serial#,
          p.spid,     s.machine, s.process, s.lockwait
  from    v$session s, v$process p
  where   s.username = upper('&user_id')
  and     s.paddr    = p.addr 
                                     ORACLE             USER
                                     SERVER    APPL.    APPL.
USERNAME     STATUS      SID SERIAL# PROCESS   MACHINE  PROCESS   LOCKWAIT
------------ -------- ------ ------- --------- -------- --------- --------
MPOWEL01     ACTIVE       14   32293 4164      seqdev   4133

Once the sid and serial# have been obtained the Oracle provided package dbms_system procedure set_sql_trace_in_session can be called: execute sys.dbms_system.set_sql_trace_in_session(14,32293,TRUE);

The trace will automatically terminate when the session is exited, but it can be stopped earlier if desired by changing TRUE to FALSE in the set_sql_trace_in_session parameter list invocation call.

The trace file is written to the init.ora user_dump_dest parameter value, which can be found in either the init.ora file for the database or by querying v$parameter: The trace file name is normally ora_OSPID.trc, where OSPID is the Operating System process id, which is the value for the spid column of v$process for the Oracle process associated with session. See the SQL above for this information.

  select name, value
  from   v$parameter
  where  name = 'user_dump_dest'
  /
  NAME                           VALUE
  ------------------------------ -----------------------------------
  user_dump_dest                 /ora_trc/UT1/udump

On UNIX systems the command ls –ltr can be used to list the files with the most recent appearing last. To be usable tkprof needs to be executed against the trace file.

Personally I do not like to give developers direct access to the dbms_system package since developers should not generally execute some of the procedures so I place a cover package on my system. As user sys grant execute on dbms_system to the package_owner, which will be system in my example:

create or replace procedure system.trace_sql
  (
  v_session   in  varchar2,
  v_serial    in  varchar2,
  v_switch    in  varchar2  default 'START'
  )
is
v_pass_session    v$session.sid%type     ;
v_pass_serial     v$session.serial#%type ;
--
--  Cover procedure for use of Oracle provided package that can only be
--  executed by sys.   m d powell  15 Oct. 1997 for ver. 7.2.3
--
begin
v_pass_session :=  v_session ;
v_pass_serial  :=  v_serial  ;
if upper(v_switch) = 'START' then
  sys.dbms_system.set_sql_trace_in_session(v_pass_session,v_pass_serial,TRUE);
else
  sys.dbms_system.set_sql_trace_in_session(v_pass_session,v_pass_serial,FALSE);
end if;
end;
/

The ordebug utility can also be used to trace an already running system. The example that follows was executed from SQL*Plus instead of the obsolete with version 9 svrmgrl utility. Comments denoted by dashes were inserted into copy/paste of screen output.

UT1> connect internal as sysdba;
Connected.
--attach oradebug to the process
UT1> oradebug setospid 19361
Oracle pid: 16, Unix process pid: 19361, image: oracle@seqdev (TNS V1-V3)
--   turn trace on with the desired level
UT1> oradebug event 10046 trace name context forever, level 12
Statement processed.
--   turn trace off when enough data gathered
UT1> oradebug event 10046 trace name context off
Statement processed.

You can set the session using either the OS process id as in my example or using the Oracle pid: setorapid N where N is the value of the column pid in v$process for the session to be traced. In the example above that pid would have been 16 according to Oracle. The available trace levels are:

	Level
	  1 	for normal trace
	  4	bind variables
	  8	wait times	
	 12	bind variables and wait times

Oradebug is suitable for use when you need more information than the normal trace facility provides.

Further reading:

Addition information of related to finding user sessions is available in the FAQ entry: Is there a way to trace a unix process to a SID and SERIAL# ?.

As of version 8.1.7 the dbms_system package is not covered in the Oracle8i Supplied PL/SQL Packages manual.

The following resources are available on Metalink.
Note 100883.1 "How to Create a SQL Trace from Another Session"
Note 1056567.6 "Using SQL_TRACE_IN_SESSION in ORACLE Versions 7 and 8"
Note 1058210.6 HOW TO ENABLE SQL TRACE FOR ANOTHER SESSION USING ORADEBUG