VIEW: SYS.USER_SCHEDULER_RUNNING_CHAINS

Source

View Text - Preformatted

SELECT jo.name, jo.subname, cu.name, co.name, cv.var_name,
    DECODE(BITAND(jss.flags,2),2,
      DECODE(jss.status, 'K', 'PAUSED', 'F', 'PAUSED', 'R', 'RUNNING',
        'C', 'SCHEDULED', 'E', 'RETRY SCHEDULED', 'T', 'STALLED', 'P','PAUSED',
        'NOT_STARTED'),
      DECODE(jss.status, 'K', 'STOPPED', 'R', 'RUNNING', 'C', 'SCHEDULED', 'E',
        'RETRY SCHEDULED', 'T', 'STALLED', 'P', 'PAUSED', 'F',
        DECODE(jss.error_code,0,'SUCCEEDED','FAILED'), 'NOT_STARTED')),
    jss.error_code, DECODE(jss.status, 'F', 'TRUE', 'K', 'TRUE','FALSE'),
    jss.start_date, jss.end_date,
    (CASE WHEN jss.end_date>jss.start_date THEN jss.end_date-jss.start_date
       ELSE NULL END),
    DECODE(BITAND(jss.flags,1),0,'FALSE',1,'TRUE',
      DECODE(BITAND(cv.flags,1),0,'FALSE',1,'TRUE')),
    DECODE(BITAND(jss.flags,2),0,'FALSE',2,'TRUE',
      DECODE(BITAND(cv.flags,2),0,'FALSE',2,'TRUE')),
    DECODE(BITAND(jss.flags,512),0,'FALSE',512,'TRUE',
      DECODE(BITAND(cv.flags,512),0,'FALSE',512,'TRUE')),
    DECODE(BITAND(jss.flags,64),0,'FALSE',64,'TRUE',
      DECODE(BITAND(cv.flags,64),0,'FALSE',64,'TRUE')),
    DECODE(BITAND(jss.flags,128),0,'FALSE',128,'TRUE',
      DECODE(BITAND(cv.flags,128),0,'FALSE',128,'TRUE')),
    jso.subname, jss.job_step_log_id
  FROM sys.scheduler$_job j
     JOIN obj$ jo ON (j.obj# = jo.obj# AND jo.owner# = USERENV('SCHEMAID'))
     JOIN obj$ co ON (co.obj# = j.program_oid)
     JOIN user$ cu ON (co.owner# = cu.user#)
     JOIN scheduler$_step cv ON (cv.oid = j.program_oid)
     LEFT OUTER JOIN scheduler$_step_state jss
       ON (jss.job_oid = j.obj# AND jss.step_name = cv.var_name)
     LEFT OUTER JOIN obj$ jso ON (jss.job_step_oid = jso.obj#)
     WHERE (BITAND(j.job_status,2+256) != 0 OR jo.subname IS NOT NULL)

View Text - HTML Formatted

SELECT JO.NAME
, JO.SUBNAME
, CU.NAME
, CO.NAME
, CV.VAR_NAME
, DECODE(BITAND(JSS.FLAGS
, 2)
, 2
, DECODE(JSS.STATUS
, 'K'
, 'PAUSED'
, 'F'
, 'PAUSED'
, 'R'
, 'RUNNING'
, 'C'
, 'SCHEDULED'
, 'E'
, 'RETRY SCHEDULED'
, 'T'
, 'STALLED'
, 'P'
, 'PAUSED'
, 'NOT_STARTED')
, DECODE(JSS.STATUS
, 'K'
, 'STOPPED'
, 'R'
, 'RUNNING'
, 'C'
, 'SCHEDULED'
, 'E'
, 'RETRY SCHEDULED'
, 'T'
, 'STALLED'
, 'P'
, 'PAUSED'
, 'F'
, DECODE(JSS.ERROR_CODE
, 0
, 'SUCCEEDED'
, 'FAILED')
, 'NOT_STARTED'))
, JSS.ERROR_CODE
, DECODE(JSS.STATUS
, 'F'
, 'TRUE'
, 'K'
, 'TRUE'
, 'FALSE')
, JSS.START_DATE
, JSS.END_DATE
, (CASE WHEN JSS.END_DATE>JSS.START_DATE THEN JSS.END_DATE-JSS.START_DATE ELSE NULL END)
, DECODE(BITAND(JSS.FLAGS
, 1)
, 0
, 'FALSE'
, 1
, 'TRUE'
, DECODE(BITAND(CV.FLAGS
, 1)
, 0
, 'FALSE'
, 1
, 'TRUE'))
, DECODE(BITAND(JSS.FLAGS
, 2)
, 0
, 'FALSE'
, 2
, 'TRUE'
, DECODE(BITAND(CV.FLAGS
, 2)
, 0
, 'FALSE'
, 2
, 'TRUE'))
, DECODE(BITAND(JSS.FLAGS
, 512)
, 0
, 'FALSE'
, 512
, 'TRUE'
, DECODE(BITAND(CV.FLAGS
, 512)
, 0
, 'FALSE'
, 512
, 'TRUE'))
, DECODE(BITAND(JSS.FLAGS
, 64)
, 0
, 'FALSE'
, 64
, 'TRUE'
, DECODE(BITAND(CV.FLAGS
, 64)
, 0
, 'FALSE'
, 64
, 'TRUE'))
, DECODE(BITAND(JSS.FLAGS
, 128)
, 0
, 'FALSE'
, 128
, 'TRUE'
, DECODE(BITAND(CV.FLAGS
, 128)
, 0
, 'FALSE'
, 128
, 'TRUE'))
, JSO.SUBNAME
, JSS.JOB_STEP_LOG_ID
FROM SYS.SCHEDULER$_JOB J JOIN OBJ$ JO ON (J.OBJ# = JO.OBJ#
AND JO.OWNER# = USERENV('SCHEMAID')) JOIN OBJ$ CO ON (CO.OBJ# = J.PROGRAM_OID) JOIN USER$ CU ON (CO.OWNER# = CU.USER#) JOIN SCHEDULER$_STEP CV ON (CV.OID = J.PROGRAM_OID) LEFT OUTER JOIN SCHEDULER$_STEP_STATE JSS ON (JSS.JOB_OID = J.OBJ#
AND JSS.STEP_NAME = CV.VAR_NAME) LEFT OUTER JOIN OBJ$ JSO ON (JSS.JOB_STEP_OID = JSO.OBJ#)
WHERE (BITAND(J.JOB_STATUS
, 2+256) != 0 OR JO.SUBNAME IS NOT NULL)