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:
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SESSION SET STATISTICS_LEVEL=ALL;
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))’)
