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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
— 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; |