SQL, Extract From Process

Overview

The instructions show how to enable a trace to extract all the SQL being executed by a particular process or job.

Procedure

 -- Place Tracing Wrapper Around Job
 alter session set tracefile_identifier='10046traceNow';
 alter session set timed_statistics = true;
 alter session set statistics_level=all;
 alter session set max_dump_file_size = unlimited;
 alter session set events '10046 trace name context forever,level 12';
 -- execute the query
 alter session set events '10046 trace name context off';
 10046 tracing for session which has already began.

 -- To start tracing:
 exec dbms_system.set_ev(SID, Serial#, 10046, 12, '');

 -- To stop tracing:
 exec dbms_system.set_ev(SID, Serial#, 10046, 0, '');