Category: System Administrator

Query To get On Hold Concurrent Request in Oracle Apps R12

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'

FND_CONCURRENT_REQUESTS Status Codes and Phase Code values

We execute very frequently SQL on the table – FND_CONCURRENT_REQUESTS to get the details on the Concurrent details to analysis and know the parameters and other details but not sure if we actually have details on all  Status Codes and Phase Codes getting used in the table – FND_CONCURRENT_REQUESTS

MEANING FOR THE PHASE_CODE COLUMN VALUES:
C Completed
I Inactive
P Pending
R Running

MEANING FOR THE STATUS_CODE COLUMN VALUES:
A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting