Trace an arbitrary Oracle application session in client/server architecture


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))’)

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Follow

Get every new post delivered to your Inbox.

%d bloggers like this: