Tracing your Oracle session

How to trace Oracle session using SQL and PL/SQL tools?

Many times developers and/or DBA’s want to trace their own and/or other session’s  SQL and PL/SQL activities.
There are many possibilities to solve this problem especially, if you  want to trace your  session.
Very easy to identify which is your session:


Let us suppose that  your session is identified by SID=403 and SERIAL#=1242.
Your Operating system process identifier (SPID) is 8564
You can find the directory of the trace file which will be created later:

Modify your session:


Execute the following query:

Under the trace directory You will find the following file: orcl_ora_8564.trc where 8564 is your 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_8564.trc hr_sqlplus.txt

Detail from the text file:

Executing  a suitable PL/SQL stored procedure, called  SQLID:

You want to find your SELECT statement’s execution plan with plan statistics (due to statistics level is ALL!)
EXEC SQLID(‘MAX(SUM(salary))’)


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: