How to set trace on procedure package session level..

Hi All,

Today I will share the syntax, how to put to trace on  procedure package session level..


1. Procedure Package level:

As we all aware any proc / package start with BEGIN & end with END, hence to put a trace find following approach.

  BEGIN
execute immediate 'ALTER SESSION SET sql_trace = TRUE';-- this will enable trace
execute immediate 'ALTER SESSION SET TRACEFILE_IDENTIFIER ="XYZ_NAME"';-- this to identify trace file name in user dump dest.

SQL statements;


  execute immediate 'ALTER SESSION SET sql_trace=FALSE'; -- this will disable trace
END;

As you see above, when you want to enable trace put above statement withing pl/sql block as mentioned above,

Trace file will be generated in dump dest location with extention .trc file. and file name will have XYZ_NAME



2. Session level trace

If you want to generate trace  on session level refer below steps

on sql prompt initiate the trace=true

alter session set sql_trace = true;
alter session set tracefile_indentfier="XYZ_NAME";

select distinct sid from v$mystat;  -- this will give sid of your own session
select a.sid,b.spid from v$session a, v$process b where a.paddr = b.addr and a.sid = &SID;  -- this will give spid (OS process id) of your session

your sql statements/proc/package execution

alter session set sql_trace = false;


Trace file will be generated in dump dest location with extention .trc file. and file name will have XYZ_NAME


This trace file have to convert into readable format, with Oracle tkprof utility.

tkprof XYZ_NAME.trc    XYZ_NAME.txt sys=no