Long Running Programs – SQL Query to Find Current Execution Stage of a Concurrent Request

We can find current execution stage of any PL/SQL code from table GV$SQLTEXT_WITH_NEWLINES but we need its session ID. If that PL/SQL code belongs to a concurrent request, you can use below query to find Session ID (SID) of a concurrent request.

SELECT a.request_id,
       d.sid,
       d.serial#,
       d.osuser,
       d.process,
       c.SPID,
       d.inst_id
  FROM apps.fnd_concurrent_requests   a,
       apps.fnd_concurrent_processes  b,
       gv$process                     c,
       gv$session                     d
 WHERE     a.controlling_manager = b.concurrent_process_id
       AND c.pid = b.oracle_process_id
       AND b.session_id = d.audsid
       AND a.request_id = :request_id
       --AND a.phase_code = 'R'
;

Below is the query to find current execution stage of a PL/SQL Code using its Session ID

  SELECT S.USERNAME,
         s.sid,
         s.osuser,
         t.sql_id,
         sql_text
    FROM gv$sqltext_with_newlines t, gV$SESSION s
   WHERE     t.address = s.sql_address
         AND t.hash_value = s.sql_hash_value
         AND s.status = 'ACTIVE'
         AND s.username <> 'SYSTEM'
         AND s.sid = :Session_ID
ORDER BY s.sid, t.piece;