Query To get On Hold Concurrent Request -oracelappshelp.com

Hi All,

Please find below the query to identify the HOLD Requests by mapping with the below given tables to get the session and process specific details for the Running Concurrent Requests having Hold.

APPS.FND_CONCURRENT_REQUESTS
GV$PROCESS
GV$SESSION
GV$LOCKED_OBJECT
GV$LOCK
DBA_OBJECTS

QUERY TO GET THE HOLDS ON THE CONCURRENT REQUESTS:

SELECT
FCR.REQUEST_ID,
FCR.HOLD_FLAG,
GV_SESSION.SERVICE_NAME,
GV_SESSION.SQL_TRACE,
GV_PROCESS.PROGRAM,
GV_SESSION.MODULE,
GV_SESSION.ACTION,
GV_PROCESS.ADDR,
GV_PROCESS.PGA_USED_MEM,
GV_PROCESS.PGA_ALLOC_MEM,
GV_SESSION.LOCKWAIT,
GV_SESSION.STATUS SESSION_STATUS,
GV_SESSION.MACHINE,
GV_SESSION.PORT,
GV_SESSION.CLIENT_IDENTIFIER,
GV_SESSION.BLOCKING_SESSION_STATUS IS_SESSION_BLOCKED,
GV_SESSION.WAIT_CLASS,
GV_SESSION.SECONDS_IN_WAIT,
GV_LOCK_OBJ.OBJECT_ID,
GV_LOCK_OBJ.SESSION_ID,
OBJECT_NAME,
OBJECT_TYPE,
DECODE( GV_LOCK.BLOCK
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global'
) STATUS
,      DECODE( GV_LOCK_OBJ.LOCKED_MODE
, 0, 'None'
, 1, 'Null'
, 2, 'Row-Share (SS)'
, 3, 'Row-Xclusive (SX)'
, 4, 'Share'
, 5, 'S/Row-Xclusive (SSX)'
, 6, 'Exclusive'
, TO_CHAR(LMODE)
) MODE_HELD
FROM   APPS.FND_CONCURRENT_REQUESTS FCR
,      GV$PROCESS GV_PROCESS
,      GV$SESSION GV_SESSION
,      GV$LOCKED_OBJECT GV_LOCK_OBJ
,      GV$LOCK GV_LOCK
,      DBA_OBJECTS DBA_OBJ
WHERE 1=1
AND FCR.PHASE_CODE = 'R'
AND    FCR.ORACLE_PROCESS_ID = GV_PROCESS.SPID (+)
AND    GV_PROCESS.ADDR = GV_SESSION.PADDR (+)
AND    GV_SESSION.SID = GV_LOCK_OBJ.SESSION_ID (+)
AND    GV_LOCK_OBJ.OBJECT_ID = DBA_OBJ.OBJECT_ID (+)
AND    GV_LOCK_OBJ.OBJECT_ID = GV_LOCK.ID1 (+)
AND    FCR.HOLD_FLAG ='Y'

Query To get On Hold Concurrent Request in Oracle Apps R12