The blog provides 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'