VIEW: SYS.ALL_SCHEDULER_REMOTE_JOBSTATE

Source

View Text - Preformatted

SELECT u.name, o.name, j.destination,
        DECODE(BITAND(j.job_status,1+2+4+8+16+32+8192),0,'DISABLED',1,
        (CASE WHEN j.retry_count>0 THEN 'RETRY SCHEDULED' ELSE 'SCHEDULED' END),
        2, 'RUNNING',
        4,'COMPLETED',8,'BROKEN',16,'FAILED',
        32,'SUCCEEDED' ,8192, 'STOPPED', NULL),
	j.next_start_date, j.run_count,
        j.failure_count, j.retry_count,
        j.last_start_date, j.last_end_date
 FROM user$ u, obj$ o, scheduler$_remote_job_state j
 WHERE j.joboid = o.obj# and o.owner# = u.user# and
    (o.owner# = USERENV('SCHEMAID') or
     o.obj# in
        (select oa.obj#
             from sys.objauth$ oa
              where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
            )
       or /* user has system privileges */
         (exists (select null from v$enabledprivs
                 where priv_number = -265 /* CREATE ANY JOB */
                 )
          and o.owner#!=0)
       )

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, J.DESTINATION
, DECODE(BITAND(J.JOB_STATUS
, 1+2+4+8+16+32+8192)
, 0
, 'DISABLED'
, 1
, (CASE WHEN J.RETRY_COUNT>0 THEN 'RETRY SCHEDULED' ELSE 'SCHEDULED' END)
, 2
, 'RUNNING'
, 4
, 'COMPLETED'
, 8
, 'BROKEN'
, 16
, 'FAILED'
, 32
, 'SUCCEEDED'
, 8192
, 'STOPPED'
, NULL)
, J.NEXT_START_DATE
, J.RUN_COUNT
, J.FAILURE_COUNT
, J.RETRY_COUNT
, J.LAST_START_DATE
, J.LAST_END_DATE
FROM USER$ U
, OBJ$ O
, SCHEDULER$_REMOTE_JOB_STATE J
WHERE J.JOBOID = O.OBJ#
AND O.OWNER# = U.USER# AND (O.OWNER# = USERENV('SCHEMAID') OR O.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR /* USER HAS SYSTEM PRIVILEGES */ (EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -265 /* CREATE ANY JOB */ )
AND O.OWNER#!=0) )