|Author's name: Mark D Powell
Author's Email: Mark.Powell@eds.com
|Date written: 27th July 2001
Oracle version(s): 184.108.40.206
|How do I switch on sql_trace in another session that is already running ?|
Back to index of questions
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.
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
Back to top
Back to index of questions