SQL, Get for Running Process
Overview
Get the SQL statement from a running process.
Methods
Oracle Reports
SQL_ID
SELECT * FROM dba_hist_sqltext s WHERE s.sql_id = 'c53k9q5pxkdam';
SQL Developer
Via Real Time SQL Monitor.
- From SQL Developer run: Tools 🠊 Real Time SQL Monitor
- Sort as required to see your SQL statement:
Status: if still running.
Duration or Database Time: to see by long running SQL.
Start Time: if you know this can be helpful too.
This will only grab part of the SQL if it is long.
Trace
The instructions show how to enable a trace to extract all the SQL being executed by a particular process or job.
-- 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, '');