Let us suppose that you want to trace a session which was established in client/server architecture (Dedicated Server architecture)
Tracing a session
This method is applicable, if the session was created by an c/s application.
Very easy to identify which is the target session:
Let us suppose that the session is identified by
SID=420 and SERIAL#=2103.
The HR user’s Operating system process identifier (SPID) is 6684.
You can find the directory of the trace file which will be created later:
Now you can execute (as SYS) the following PL/SQL program:
EXEC DBMS_MONITOR.session_trace_enable(420,2103,TRUE,TRUE,’ALL_EXECUTIONS’)
3. argument (waits) TRUE, wait information is present in the trace
4. argument (binds) TRUE, bind information is present in the trace
If you want to set the statistics level for that session
create an AFTER LOGON trigger, like this:
CREATE OR REPLACE TRIGGER logon_trig
AFTER LOGON ON hr.SCHEMA
BEGIN
EXECUTE IMMEDIATE
‘ALTER SESSION SET STATISTICS_LEVEL=ALL’;
END;
/
Connect and execute the following query as HR :
You can turn off the tracing for the same session:
EXEC DBMS_MONITOR.session_disable(420,2103)
Under the trace directory you will find the following file: orcl_ora_6684.trc where 6684 is HR’s SPID.
This file contains very detailed information.
If You need a more readable text file, please run the tkprof utility in command line
tkprof orcl_ora_6684.trc hr_sqlplus2.txt
Detail from the file:
Execute the sqlid stored procedure
(which can be found in “Trace your Oracle session” blog)
as SYS:
EXEC hr.sqlid(‘MAX(SUM(salary))’)
