Get SQL for a concurrent request id

1. Use this Script to get current sql statement that is running in the background for a given concurrent request.
2. Get all the sql statements that are being executed by a user.
3. Get all the blocking sessions

-- GET THE CURRENT SQL STATEMENT RUNNING FOR A CONCURRENT REQUEST

 

SELECT A.REQUEST_ID, D.SID, D.SERIAL#, D.OSUSER, D.PROCESS, C.SPID,

       E.SQL_TEXT

  FROM APPS.FND_CONCURRENT_REQUESTS A,

       APPS.FND_CONCURRENT_PROCESSES B,

       V$PROCESS C,

       V$SESSION D,

       V$SQL E

WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID

   AND C.PID = B.ORACLE_PROCESS_ID

   AND B.SESSION_ID = D.AUDSID

   AND D.SQL_ADDRESS = E.ADDRESS

   AND A.REQUEST_ID = &REQUEST_ID;

 

 

--SQL STATEMENTS RUNNING BY A USER  

SELECT A.SID, A.SERIAL#, B.SQL_TEXT

  FROM V$SESSION A, V$SQLAREA B

WHERE A.SQL_ADDRESS = B.ADDRESS AND A.USERNAME = 'APPS';

 

 

-- GET THE BLOCKING SESSIONS    

SELECT   BLOCKING_SESSION, SID, SERIAL#, WAIT_CLASS, SECONDS_IN_WAIT

    FROM V$SESSION

   WHERE BLOCKING_SESSION IS NOT NULL

ORDER BY BLOCKING_SESSION;