SQL, Get for Running Process

Overview

Get the SQL statement from a running process.

Methods

Oracle Reports

• ASH
• AWR

Get Full SQL

SET SERVEROUTPUT ON SIZE UNLIMITED
spool H:\app\sql\spool\sql-cmd.txt

DECLARE
  l_full_sql CLOB;
BEGIN
  FOR rec IN (
    SELECT sql_text
    FROM v$sqltext_with_newlines
    WHERE sql_id = '89qnqv6vna88m'
    ORDER BY piece
  ) LOOP
    l_full_sql := l_full_sql || rec.sql_text;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE(l_full_sql);
END;
/

spool off

If SQL Cmd Small

SQL_ID

 -- Option 1
 SELECT * FROM dba_hist_sqltext s WHERE s.sql_id = 'c53k9q5pxkdam';
 -- Option 2
 SELECT sql_text FROM v$sql WHERE sql_id = 'c53k9q5pxkdam'; 

SQL Developer

Via Real Time SQL Monitor.

1. From SQL Developer run: Tools 🠊 Real Time SQL Monitor
2. Sort as required to see your SQL statement:

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, '');