Friday, November 2, 2018

Get SQL for a concurrent request id

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;

No comments:

Post a Comment

SQL Important Queries

  How to delete rows with no where clause The following example deletes  all rows  from the  Person.Person  the table in the AdventureWork...