eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: SYS.ALL_SCHEDULER_JOBS

Source

View Text - Preformatted


SELECT ju.name, jo.name, jo.subname, 'REGULAR', j.creator, j.client_id, j.guid,
    DECODE(bitand(j.flags,4194304),4194304,
      substr(j.program_action,1,instr(j.program_action,'"')-1),NULL),
    /* PROGRAM_NAME */
    DECODE(bitand(j.flags,4194304),4194304,
      substr(j.program_action,instr(j.program_action,'"')+1,
        length(j.program_action)-instr(j.program_action,'"')) ,NULL),
    /* JOB_TYPE */
    DECODE(BITAND(j.flags,131072+262144+2097152+524288+281474976710656+
                          562949953421312+1125899906842624),
      131072, 'PLSQL_BLOCK', 262144, 'STORED_PROCEDURE',
      2097152, 'EXECUTABLE', 524288, 'CHAIN',
      281474976710656, 'EXTERNAL_SCRIPT', 562949953421312, 'SQL_SCRIPT',
      1125899906842624, 'BACKUP_SCRIPT', NULL),
    /* JOB_ACTION */
    DECODE(bitand(j.flags,4194304),0,j.program_action,NULL),
    /* NUMBER_OF_ARGUMENTS */
    j.number_of_args,
    /* SCHEDULER_OWNER */
    DECODE(bitand(j.flags,1024+4096),0,NULL,
      substr(j.schedule_expr,1,instr(j.schedule_expr,'"')-1)),
    /* SCHEDULE_NAME */
    DECODE(bitand(j.flags,1024+4096),0,NULL,
      substr(j.schedule_expr,instr(j.schedule_expr,'"') + 1,
        length(j.schedule_expr)-instr(j.schedule_expr,'"'))),
    /* SCHEDULE_TYPE */
    DECODE(BITAND(j.flags, 1+2+512+1024+2048+4096+8192+16384+134217728+34359738368),
      512,'PLSQL',1024,'NAMED',2048,'CALENDAR',4096,'WINDOW',4098,'WINDOW_GROUP',
      8192,'ONCE',16384,'IMMEDIATE',34493956096, 'FILE_WATCHER',
      134217728,'EVENT',NULL),
    /* START_DATE */
    j.start_date,
    /* REPEAT_INTERVAL */
    DECODE(BITAND(j.flags,1024+4096+134217728), 0, j.schedule_expr, NULL),
    /* EVENT_QUEUE_OWNER */
    j.queue_owner,
    /* EVENT_QUEUE_NAME */
    j.queue_name,
    /* EVENT_QUEUE_AGENT */
    j.queue_agent,
    /* EVENT_CONDITION */
    DECODE(BITAND(j.flags,134217728), 0, NULL,
      DECODE(BITAND(j.flags,1024+4096), 0, j.schedule_expr, NULL)),
    /* EVENT_RULE */
    j.event_rule,
    /* FILE_WATCHER_OWNER */
    DECODE(BITAND(j.flags, 34359738368), 0, NULL,
      substr(j.fw_name,1,instr(j.fw_name,'"')-1)),
    /* FILE_WATCHER_NAME */
    DECODE(BITAND(j.flags, 34359738368), 0, NULL,
      substr(j.fw_name,instr(j.fw_name,'"') + 1,
        length(j.fw_name)-instr(j.fw_name,'"'))),
    /* END_DATE */
    j.end_date,
    /* JOB_CLASS */
    co.name,
    /* ENABLED */
    DECODE(BITAND(j.job_status,1),0,'FALSE','TRUE'),
    /* AUTO_DROP */
    DECODE(BITAND(j.flags,32768),0,'TRUE','FALSE'),
    /* RESTART_ON_RECOVERY */
    DECODE(BITAND(j.flags, 35184372088832),0,'FALSE','TRUE'),
    /* RESTART_ON_FAILURE */
    DECODE(BITAND(j.flags, 70368744177664),0,'FALSE','TRUE'),
    /* STATE */
    (CASE WHEN j.job_dest_id <> 0 AND
     bitand(j.flags, 549755813888) <> 0 THEN 'RUNNING'
     ELSE
    DECODE(BITAND(j.job_status,2+65536),2,'RUNNING',2+65536,'CHAIN_STALLED',
    DECODE(BITAND(j.job_status,1+4+8+16+32+128+8192+524288),0,'DISABLED',1,
      (CASE WHEN j.retry_count>0  AND bitand(j.flags, 549755813888) = 0
            THEN 'RETRY SCHEDULED'
            WHEN (bitand(j.job_status, 1024) <> 0) THEN 'READY TO RUN'
            ELSE 'SCHEDULED' END),
      4,'COMPLETED',8,'BROKEN',16,'FAILED',
      32,'SUCCEEDED' ,128,'REMOTE',8192, 'STOPPED',
      524288, 'SOME FAILED', NULL)) END),
    j.priority, j.run_count,  j.max_runs,
    j.failure_count,  j.max_failures,
    decode(bitand(j.flags, 549755813888), 0, j.retry_count, 0),
    /* LAST_START_DATE */
    j.last_start_date,
    /* LAST_RUN_DURATION*/
    (CASE WHEN j.last_end_date>j.last_start_date THEN j.last_end_date-j.last_start_date
       ELSE NULL END),
    /* NEXT_RUN_DATE */
    j.next_run_date,
    /* SCHEDULER_LIMIT */
    j.schedule_limit,
    /* MAX_RUN_DURATION */
    j.max_run_duration,
    /* LOGGING_LEVEL */
    DECODE(BITAND(j.flags,32+64+128+256),32,'OFF',64,'RUNS',128,'FAILED RUNS',
      256,'FULL',NULL),
    /* STORE_OUTPUT */
    DECODE(BITAND(j.flags,9007199254740992),0,'FALSE','TRUE'),
    /* STOP_ON_WINDOW_CLOSE */
    DECODE(BITAND(j.flags,8),0,'FALSE','TRUE'),
    /* INSTANCE_STICKINESS */
    DECODE(BITAND(j.flags,16),0,'FALSE','TRUE'),
    /* RAISE_EVENTS */
    sys.dbms_scheduler.generate_event_list(j.job_status),
    /* SYSTEM */
    DECODE(BITAND(j.flags,16777216),0,'FALSE','TRUE'),
    /* JOB_WEIGHT */
    j.job_weight,
    /* NLS_ENV */
    j.nls_env,
    /* SOURCE */
    j.source,
    /* NUMBER_OF_DESTINATIONS */
    decode(bitand(j.flags, 274877906944), 0, 1,
    decode(bitand(j.flags, 549755813888), 0, 1,
    (select count(*) from all_scheduler_job_dests ajd
     where ajd.owner = ju.name and ajd.job_name = jo.name))),
    /* DESTINATION_OWNER */
    decode(bitand(j.flags, 274877906944), 0, NULL,
       substr(j.destination, 1, instr(j.destination, '"')-1)),
    /* DESTINATION */
    decode(bitand(j.flags, 274877906944), 0, j.destination,
    substr(j.destination, instr(j.destination, '"')+1,
           length(j.destination) - instr(j.destination, '"'))),
    /* CREDENTIAL_OWNER */
    j.credential_owner,
    /* CREDENTIAL_NAME */
    j.credential_name,
    /* INSTANCE_ID */
    j.instance_id,
    /* DEFERRED_DROP */
    DECODE(BITAND(j.job_status,131072),0,'FALSE','TRUE'),
    /* ALLOW_RUNS_IN_RESTRICTED_MODE */
    DECODE(BITAND(j.flags,17179869184),0,'FALSE','TRUE'),
    /* COMMENTS */
    j.comments,
    /* FLAGS*/
    j.flags,
    /* RESTARTABLE */
    DECODE(BITAND(j.flags,35184372088832 + 70368744177664),
            35184372088832 + 70368744177664,'TRUE','FALSE'),
    /* connect credential owner and name */
    j.connect_credential_owner, j.connect_credential_name
  FROM obj$ jo, user$ ju, sys.scheduler$_job j, obj$ co,
        (select /*+ no_merge */ database_role from v$database) v
  WHERE j.obj# = jo.obj# AND jo.owner# = ju.user# AND
    j.class_oid = co.obj#(+) AND
   ( (j.database_role = v.database_role) OR
     (j.database_role = 'ALL')           OR
     (j.database_role is null AND v.database_role = 'PRIMARY')) AND
    (jo.owner# = userenv('SCHEMAID')
       or jo.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 in (-265 /* CREATE ANY JOB */,
                                          -255 /* EXPORT FULL DATABASE */ )
                 )
          and jo.owner#!=0)
       )
 UNION ALL
  SELECT
    /* OWNER */
    lu.name,
    /* JOB_NAME */
    lo.name,
    /* JOB_SUBNAME */
    lo.subname,
    /* JOB_STYLE */
    decode(bitand(l.flags, 17592186044416), 0, 'LIGHTWEIGHT',
      'IN_MEMORY_RUNTIME'),
    /* JOB_CREATOR */
    l.creator,
    /* CLIENT_ID */
    l.client_id,
    /* GLOBAL_UID */
    l.guid,
    /* PROGRAM_OWNER */
    lu.name,
    /* PROGRAM_NAME */
    po.name,
    /* JOB_TYPE */
    NULL,
    /* JOB_ACTION */
    NULL,
    /* NUMBER_OF_ARGUMENTS */
    NULL,
    /* SCHEDULER_OWNER */
    DECODE(bitand(l.flags,1024+4096),0,NULL,
      substr(l.schedule_expr,1,instr(l.schedule_expr,'"')-1)),
    /* SCHEDULE_NAME */
    DECODE(bitand(l.flags,1024+4096),0,NULL,
      substr(l.schedule_expr,instr(l.schedule_expr,'"') + 1,
        length(l.schedule_expr)-instr(l.schedule_expr,'"'))),
    /* SCHEDULE_TYPE */
    DECODE(BITAND(l.flags, 1+2+512+1024+2048+4096+8192+16384+134217728+34359738368),
      512,'PLSQL',1024,'NAMED',2048,'CALENDAR',4096,'WINDOW',4098,'WINDOW_GROUP',
      8192,'ONCE',16384,'IMMEDIATE',34493956096, 'FILE_WATCHER',
      134217728,'EVENT',NULL),
    /* START_DATE */
    l.start_date,
    /* REPEAT_INTERVAL */
    DECODE(BITAND(l.flags,1024+4096+134217728), 0, l.schedule_expr, NULL),
    /* EVENT_QUEUE_OWNER */
    l.queue_owner,
    /* EVENT_QUEUE_NAME */
    l.queue_name,
    /* EVENT_QUEUE_AGENT */
    l.queue_agent,
    /* EVENT_CONDITION */
    DECODE(BITAND(l.flags,134217728), 0, NULL,
      DECODE(BITAND(l.flags,1024+4096), 0, l.schedule_expr, NULL)),
    /* EVENT_RULE */
    l.event_rule,
    /* FILE_WATCHER_OWNER */
    DECODE(BITAND(l.flags, 34359738368), 0, NULL,
      substr(l.fw_name,1,instr(l.fw_name,'"')-1)),
    /* FILE_WATCHER_NAME */
    DECODE(BITAND(l.flags, 34359738368), 0, NULL,
      substr(l.fw_name,instr(l.fw_name,'"') + 1,
        length(l.fw_name)-instr(l.fw_name,'"'))),
    /* END_DATE */
    l.end_date,
    /* JOB_CLASS */
    lco.name,
    /* ENABLED */
    DECODE(BITAND(l.job_status,1),0,'FALSE','TRUE'),
    /* AUTO_DROP */
    DECODE(BITAND(l.flags,32768),0,'TRUE','FALSE'),
    /* RESTART_ON_RECOVERY */
    DECODE(BITAND(l.flags, 35184372088832),0,'FALSE','TRUE'),
    /* RESTART_ON_FAILURE */
    DECODE(BITAND(l.flags, 70368744177664),0,'FALSE','TRUE'),
    /* STATE */
    DECODE(BITAND(l.job_status,2+65536),2,'RUNNING',2+65536,'CHAIN_STALLED',
    DECODE(BITAND(l.job_status,1+4+8+16+32+128+8192),0,'DISABLED',1,
      (CASE WHEN l.retry_count>0 THEN 'RETRY SCHEDULED'
            WHEN (bitand(l.job_status, 1024) <> 0) THEN 'READY TO RUN'
            ELSE 'SCHEDULED' END),
      4,'COMPLETED',8,'BROKEN',16,'FAILED',
      32,'SUCCEEDED' ,128,'REMOTE',8192, 'STOPPED', NULL)),
    NULL,
           l.run_count, NULL,
           l.failure_count,
    NULL,
    /* RETRY_COUNT */
    l.retry_count,
    /* LAST_START_DATE */
    l.last_start_date,
    /* LAST_RUN_DURATION*/
    (CASE WHEN l.last_end_date>l.last_start_date THEN l.last_end_date-l.last_start_date
       ELSE NULL END),
    /* NEXT_RUN_DATE */
    l.next_run_date,
    /* SCHEDULER_LIMIT */
    NULL,
    /* MAX_RUN_DURATION */
    NULL,
    /* LOGGING_LEVEL */
    DECODE(BITAND(l.flags,32+64+128+256),32,'OFF',64,'RUNS',128,'FAILED RUNS',
      256,'FULL',NULL),
    /* STORE_OUTPUT */
    DECODE(BITAND(l.flags,9007199254740992),0,'FALSE','TRUE'),
    /* STOP_ON_WINDOW_CLOSE */
    DECODE(BITAND(l.flags,8),0,'FALSE','TRUE'),
    /* INSTANCE_STICKINESS */
    DECODE(BITAND(l.flags,16),0,'FALSE','TRUE'),
    /* RAISE_EVENTS */
    sys.dbms_scheduler.generate_event_list(l.job_status),
    /* SYSTEM */
    DECODE(BITAND(l.flags,16777216),0,'FALSE','TRUE'),
    /* JOB_WEIGHT */
    NULL,
    /* NLS_ENV */
    NULL,
    /* SOURCE */
    NULL,
    /* NUMBER_OF_DESTINATIONS */
    1,
    /* DESTINATION_OWNER */
    NULL,
    /* DESTINATION */
    NULL,
    /* CREDENTIAL_OWNER */
    NULL,
    /* CREDENTIAL_NAME */
    NULL,
    /* INSTANCE_ID */
    l.instance_id,
    /* DEFERRED_DROP */
    DECODE(BITAND(l.job_status,131072),0,'FALSE','TRUE'),
    /* ALLOW_RUNS_IN_RESTRICTED_MODE */
    DECODE(BITAND(l.flags,17179869184),0,'FALSE','TRUE'),
    /* COMMENTS */
    NULL,
    /* FLAGS*/
    l.flags,
    /* RESTARTABLE */
    DECODE(BITAND(l.flags,35184372088832 + 70368744177664),
            35184372088832 + 70368744177664,'TRUE','FALSE'),
    NULL, NULL
  FROM scheduler$_lwjob_obj lo, user$ lu, obj$ lco,
    scheduler$_comb_lw_job l, obj$ po
  WHERE ((bitand(l.flags, 8589934592) = 0 AND po.type# = 67) OR
         (bitand(l.flags, 8589934592) <> 0 AND po.type# = 66))
    AND bitand(l.flags, 137438953472) = 0
    AND l.obj# = lo.obj# AND l.program_oid = po.obj#
    AND lo.userid = lu.user# AND l.class_oid = lco.obj#(+) AND
    (lo.userid = userenv('SCHEMAID') OR
     po.obj# IN
       (SELECT oa.obj#
        from sys.objauth$ oa
        where grantee# in (select kzsrorol from x$kzsro)) OR
     (EXISTS (select null from v$enabledprivs
                           where priv_number in (-265 /* CREATE ANY JOB */,
                                          -255 /* EXPORT FULL DATABASE */ )
                 )
          and lo.userid!=0))
UNION ALL
 SELECT mu.name, mr.name, NULL, 'IN_MEMORY_FULL', md.creator, md.client_id,
   md.guid, mu.name, mpo.name, NULL, NULL, NULL, NULL, NULL, NULL,
   NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
   mco.name, decode(bitand(mr.job_status, 1), 1, 'TRUE', 'FALSE'), 'TRUE',
   'FALSE', 'FALSE',
   DECODE(BITAND(mr.job_status,2+65536),2,'RUNNING',2+65536,'CHAIN_STALLED',
     DECODE(BITAND(mr.job_status,1+4+8+16+32+128+8192),0,'DISABLED',1,
      (CASE WHEN (bitand(mr.job_status, 1024) <> 0) THEN 'READY TO RUN'
            ELSE 'SCHEDULED' END),
      4,'COMPLETED',8,'BROKEN',16,'FAILED',
      32,'SUCCEEDED' ,128,'REMOTE',8192, 'STOPPED', NULL)),
   NULL, cast(NULL as number), mr.run_count, NULL,
   mr.failure_count,  NULL, mr.last_start_date,
   NULL, mr.next_run_date, NULL, NULL,
   DECODE(BITAND(md.flags,32+64+128+256),32,'OFF',64,'RUNS',128,'FAILED RUNS',
      256,'FULL',NULL),
   DECODE(BITAND(md.flags,9007199254740992),0,'FALSE','TRUE'),
   'FALSE', 'TRUE', NULL,
   DECODE(BITAND(md.flags,16777216),0,'FALSE','TRUE'),
   NULL, NULL, NULL, 1, NULL, NULL, NULL, NULL, md.instance_id, 'FALSE', 'FALSE',
   NULL, md.flags, 'FALSE', NULL, NULL
 FROM GV$SCHEDULER_INMEM_MDINFO md, GV$SCHEDULER_INMEM_RTINFO mr,
      obj$ mpo, user$ mu, obj$ mco
 WHERE md.objid = mr.objid and mpo.obj# = md.prgoid and md.clsoid = mco.obj#(+)
   AND mu.user# = mr.userid AND mr.id_type = 2 AND
   (mr.userid = userenv('SCHEMAID') OR
     mpo.obj# IN
       (SELECT oa.obj#
        from sys.objauth$ oa
        where grantee# in (select kzsrorol from x$kzsro)) OR
    (EXISTS (select null from v$enabledprivs
                           where priv_number in (-265 /* CREATE ANY JOB */,
                                          -255 /* EXPORT FULL DATABASE */ )
                 )
          and mr.userid!=0))

View Text - HTML Formatted

SELECT JU.NAME
, JO.NAME
, JO.SUBNAME
, 'REGULAR'
, J.CREATOR
, J.CLIENT_ID
, J.GUID
, DECODE(BITAND(J.FLAGS
, 4194304)
, 4194304
, SUBSTR(J.PROGRAM_ACTION
, 1
, INSTR(J.PROGRAM_ACTION
, '"')-1)
, NULL)
, /* PROGRAM_NAME */ DECODE(BITAND(J.FLAGS
, 4194304)
, 4194304
, SUBSTR(J.PROGRAM_ACTION
, INSTR(J.PROGRAM_ACTION
, '"')+1
, LENGTH(J.PROGRAM_ACTION)-INSTR(J.PROGRAM_ACTION
, '"'))
, NULL)
, /* JOB_TYPE */ DECODE(BITAND(J.FLAGS
, 131072+262144+2097152+524288+281474976710656+ 562949953421312+1125899906842624)
, 131072
, 'PLSQL_BLOCK'
, 262144
, 'STORED_PROCEDURE'
, 2097152
, 'EXECUTABLE'
, 524288
, 'CHAIN'
, 281474976710656
, 'EXTERNAL_SCRIPT'
, 562949953421312
, 'SQL_SCRIPT'
, 1125899906842624
, 'BACKUP_SCRIPT'
, NULL)
, /* JOB_ACTION */ DECODE(BITAND(J.FLAGS
, 4194304)
, 0
, J.PROGRAM_ACTION
, NULL)
, /* NUMBER_OF_ARGUMENTS */ J.NUMBER_OF_ARGS
, /* SCHEDULER_OWNER */ DECODE(BITAND(J.FLAGS
, 1024+4096)
, 0
, NULL
, SUBSTR(J.SCHEDULE_EXPR
, 1
, INSTR(J.SCHEDULE_EXPR
, '"')-1))
, /* SCHEDULE_NAME */ DECODE(BITAND(J.FLAGS
, 1024+4096)
, 0
, NULL
, SUBSTR(J.SCHEDULE_EXPR
, INSTR(J.SCHEDULE_EXPR
, '"') + 1
, LENGTH(J.SCHEDULE_EXPR)-INSTR(J.SCHEDULE_EXPR
, '"')))
, /* SCHEDULE_TYPE */ DECODE(BITAND(J.FLAGS
, 1+2+512+1024+2048+4096+8192+16384+134217728+34359738368)
, 512
, 'PLSQL'
, 1024
, 'NAMED'
, 2048
, 'CALENDAR'
, 4096
, 'WINDOW'
, 4098
, 'WINDOW_GROUP'
, 8192
, 'ONCE'
, 16384
, 'IMMEDIATE'
, 34493956096
, 'FILE_WATCHER'
, 134217728
, 'EVENT'
, NULL)
, /* START_DATE */ J.START_DATE
, /* REPEAT_INTERVAL */ DECODE(BITAND(J.FLAGS
, 1024+4096+134217728)
, 0
, J.SCHEDULE_EXPR
, NULL)
, /* EVENT_QUEUE_OWNER */ J.QUEUE_OWNER
, /* EVENT_QUEUE_NAME */ J.QUEUE_NAME
, /* EVENT_QUEUE_AGENT */ J.QUEUE_AGENT
, /* EVENT_CONDITION */ DECODE(BITAND(J.FLAGS
, 134217728)
, 0
, NULL
, DECODE(BITAND(J.FLAGS
, 1024+4096)
, 0
, J.SCHEDULE_EXPR
, NULL))
, /* EVENT_RULE */ J.EVENT_RULE
, /* FILE_WATCHER_OWNER */ DECODE(BITAND(J.FLAGS
, 34359738368)
, 0
, NULL
, SUBSTR(J.FW_NAME
, 1
, INSTR(J.FW_NAME
, '"')-1))
, /* FILE_WATCHER_NAME */ DECODE(BITAND(J.FLAGS
, 34359738368)
, 0
, NULL
, SUBSTR(J.FW_NAME
, INSTR(J.FW_NAME
, '"') + 1
, LENGTH(J.FW_NAME)-INSTR(J.FW_NAME
, '"')))
, /* END_DATE */ J.END_DATE
, /* JOB_CLASS */ CO.NAME
, /* ENABLED */ DECODE(BITAND(J.JOB_STATUS
, 1)
, 0
, 'FALSE'
, 'TRUE')
, /* AUTO_DROP */ DECODE(BITAND(J.FLAGS
, 32768)
, 0
, 'TRUE'
, 'FALSE')
, /* RESTART_ON_RECOVERY */ DECODE(BITAND(J.FLAGS
, 35184372088832)
, 0
, 'FALSE'
, 'TRUE')
, /* RESTART_ON_FAILURE */ DECODE(BITAND(J.FLAGS
, 70368744177664)
, 0
, 'FALSE'
, 'TRUE')
, /* STATE */ (CASE WHEN J.JOB_DEST_ID <> 0 AND BITAND(J.FLAGS
, 549755813888) <> 0 THEN 'RUNNING' ELSE DECODE(BITAND(J.JOB_STATUS
, 2+65536)
, 2
, 'RUNNING'
, 2+65536
, 'CHAIN_STALLED'
, DECODE(BITAND(J.JOB_STATUS
, 1+4+8+16+32+128+8192+524288)
, 0
, 'DISABLED'
, 1
, (CASE WHEN J.RETRY_COUNT>0
AND BITAND(J.FLAGS
, 549755813888) = 0 THEN 'RETRY SCHEDULED' WHEN (BITAND(J.JOB_STATUS
, 1024) <> 0) THEN 'READY TO RUN' ELSE 'SCHEDULED' END)
, 4
, 'COMPLETED'
, 8
, 'BROKEN'
, 16
, 'FAILED'
, 32
, 'SUCCEEDED'
, 128
, 'REMOTE'
, 8192
, 'STOPPED'
, 524288
, 'SOME FAILED'
, NULL)) END)
, J.PRIORITY
, J.RUN_COUNT
, J.MAX_RUNS
, J.FAILURE_COUNT
, J.MAX_FAILURES
, DECODE(BITAND(J.FLAGS
, 549755813888)
, 0
, J.RETRY_COUNT
, 0)
, /* LAST_START_DATE */ J.LAST_START_DATE
, /* LAST_RUN_DURATION*/ (CASE WHEN J.LAST_END_DATE>J.LAST_START_DATE THEN J.LAST_END_DATE-J.LAST_START_DATE ELSE NULL END)
, /* NEXT_RUN_DATE */ J.NEXT_RUN_DATE
, /* SCHEDULER_LIMIT */ J.SCHEDULE_LIMIT
, /* MAX_RUN_DURATION */ J.MAX_RUN_DURATION
, /* LOGGING_LEVEL */ DECODE(BITAND(J.FLAGS
, 32+64+128+256)
, 32
, 'OFF'
, 64
, 'RUNS'
, 128
, 'FAILED RUNS'
, 256
, 'FULL'
, NULL)
, /* STORE_OUTPUT */ DECODE(BITAND(J.FLAGS
, 9007199254740992)
, 0
, 'FALSE'
, 'TRUE')
, /* STOP_ON_WINDOW_CLOSE */ DECODE(BITAND(J.FLAGS
, 8)
, 0
, 'FALSE'
, 'TRUE')
, /* INSTANCE_STICKINESS */ DECODE(BITAND(J.FLAGS
, 16)
, 0
, 'FALSE'
, 'TRUE')
, /* RAISE_EVENTS */ SYS.DBMS_SCHEDULER.GENERATE_EVENT_LIST(J.JOB_STATUS)
, /* SYSTEM */ DECODE(BITAND(J.FLAGS
, 16777216)
, 0
, 'FALSE'
, 'TRUE')
, /* JOB_WEIGHT */ J.JOB_WEIGHT
, /* NLS_ENV */ J.NLS_ENV
, /* SOURCE */ J.SOURCE
, /* NUMBER_OF_DESTINATIONS */ DECODE(BITAND(J.FLAGS
, 274877906944)
, 0
, 1
, DECODE(BITAND(J.FLAGS
, 549755813888)
, 0
, 1
, (SELECT COUNT(*)
FROM ALL_SCHEDULER_JOB_DESTS AJD
WHERE AJD.OWNER = JU.NAME
AND AJD.JOB_NAME = JO.NAME)))
, /* DESTINATION_OWNER */ DECODE(BITAND(J.FLAGS
, 274877906944)
, 0
, NULL
, SUBSTR(J.DESTINATION
, 1
, INSTR(J.DESTINATION
, '"')-1))
, /* DESTINATION */ DECODE(BITAND(J.FLAGS
, 274877906944)
, 0
, J.DESTINATION
, SUBSTR(J.DESTINATION
, INSTR(J.DESTINATION
, '"')+1
, LENGTH(J.DESTINATION) - INSTR(J.DESTINATION
, '"')))
, /* CREDENTIAL_OWNER */ J.CREDENTIAL_OWNER
, /* CREDENTIAL_NAME */ J.CREDENTIAL_NAME
, /* INSTANCE_ID */ J.INSTANCE_ID
, /* DEFERRED_DROP */ DECODE(BITAND(J.JOB_STATUS
, 131072)
, 0
, 'FALSE'
, 'TRUE')
, /* ALLOW_RUNS_IN_RESTRICTED_MODE */ DECODE(BITAND(J.FLAGS
, 17179869184)
, 0
, 'FALSE'
, 'TRUE')
, /* COMMENTS */ J.COMMENTS
, /* FLAGS*/ J.FLAGS
, /* RESTARTABLE */ DECODE(BITAND(J.FLAGS
, 35184372088832 + 70368744177664)
, 35184372088832 + 70368744177664
, 'TRUE'
, 'FALSE')
, /* CONNECT CREDENTIAL OWNER
AND NAME */ J.CONNECT_CREDENTIAL_OWNER
, J.CONNECT_CREDENTIAL_NAME
FROM OBJ$ JO
, USER$ JU
, SYS.SCHEDULER$_JOB J
, OBJ$ CO
, (SELECT /*+ NO_MERGE */ DATABASE_ROLE
FROM V$DATABASE) V
WHERE J.OBJ# = JO.OBJ#
AND JO.OWNER# = JU.USER# AND J.CLASS_OID = CO.OBJ#(+) AND ( (J.DATABASE_ROLE = V.DATABASE_ROLE) OR (J.DATABASE_ROLE = 'ALL') OR (J.DATABASE_ROLE IS NULL
AND V.DATABASE_ROLE = 'PRIMARY')) AND (JO.OWNER# = USERENV('SCHEMAID') OR JO.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 IN (-265 /* CREATE ANY JOB */
, -255 /* EXPORT FULL DATABASE */ ) )
AND JO.OWNER#!=0) ) UNION ALL SELECT /* OWNER */ LU.NAME
, /* JOB_NAME */ LO.NAME
, /* JOB_SUBNAME */ LO.SUBNAME
, /* JOB_STYLE */ DECODE(BITAND(L.FLAGS
, 17592186044416)
, 0
, 'LIGHTWEIGHT'
, 'IN_MEMORY_RUNTIME')
, /* JOB_CREATOR */ L.CREATOR
, /* CLIENT_ID */ L.CLIENT_ID
, /* GLOBAL_UID */ L.GUID
, /* PROGRAM_OWNER */ LU.NAME
, /* PROGRAM_NAME */ PO.NAME
, /* JOB_TYPE */ NULL
, /* JOB_ACTION */ NULL
, /* NUMBER_OF_ARGUMENTS */ NULL
, /* SCHEDULER_OWNER */ DECODE(BITAND(L.FLAGS
, 1024+4096)
, 0
, NULL
, SUBSTR(L.SCHEDULE_EXPR
, 1
, INSTR(L.SCHEDULE_EXPR
, '"')-1))
, /* SCHEDULE_NAME */ DECODE(BITAND(L.FLAGS
, 1024+4096)
, 0
, NULL
, SUBSTR(L.SCHEDULE_EXPR
, INSTR(L.SCHEDULE_EXPR
, '"') + 1
, LENGTH(L.SCHEDULE_EXPR)-INSTR(L.SCHEDULE_EXPR
, '"')))
, /* SCHEDULE_TYPE */ DECODE(BITAND(L.FLAGS
, 1+2+512+1024+2048+4096+8192+16384+134217728+34359738368)
, 512
, 'PLSQL'
, 1024
, 'NAMED'
, 2048
, 'CALENDAR'
, 4096
, 'WINDOW'
, 4098
, 'WINDOW_GROUP'
, 8192
, 'ONCE'
, 16384
, 'IMMEDIATE'
, 34493956096
, 'FILE_WATCHER'
, 134217728
, 'EVENT'
, NULL)
, /* START_DATE */ L.START_DATE
, /* REPEAT_INTERVAL */ DECODE(BITAND(L.FLAGS
, 1024+4096+134217728)
, 0
, L.SCHEDULE_EXPR
, NULL)
, /* EVENT_QUEUE_OWNER */ L.QUEUE_OWNER
, /* EVENT_QUEUE_NAME */ L.QUEUE_NAME
, /* EVENT_QUEUE_AGENT */ L.QUEUE_AGENT
, /* EVENT_CONDITION */ DECODE(BITAND(L.FLAGS
, 134217728)
, 0
, NULL
, DECODE(BITAND(L.FLAGS
, 1024+4096)
, 0
, L.SCHEDULE_EXPR
, NULL))
, /* EVENT_RULE */ L.EVENT_RULE
, /* FILE_WATCHER_OWNER */ DECODE(BITAND(L.FLAGS
, 34359738368)
, 0
, NULL
, SUBSTR(L.FW_NAME
, 1
, INSTR(L.FW_NAME
, '"')-1))
, /* FILE_WATCHER_NAME */ DECODE(BITAND(L.FLAGS
, 34359738368)
, 0
, NULL
, SUBSTR(L.FW_NAME
, INSTR(L.FW_NAME
, '"') + 1
, LENGTH(L.FW_NAME)-INSTR(L.FW_NAME
, '"')))
, /* END_DATE */ L.END_DATE
, /* JOB_CLASS */ LCO.NAME
, /* ENABLED */ DECODE(BITAND(L.JOB_STATUS
, 1)
, 0
, 'FALSE'
, 'TRUE')
, /* AUTO_DROP */ DECODE(BITAND(L.FLAGS
, 32768)
, 0
, 'TRUE'
, 'FALSE')
, /* RESTART_ON_RECOVERY */ DECODE(BITAND(L.FLAGS
, 35184372088832)
, 0
, 'FALSE'
, 'TRUE')
, /* RESTART_ON_FAILURE */ DECODE(BITAND(L.FLAGS
, 70368744177664)
, 0
, 'FALSE'
, 'TRUE')
, /* STATE */ DECODE(BITAND(L.JOB_STATUS
, 2+65536)
, 2
, 'RUNNING'
, 2+65536
, 'CHAIN_STALLED'
, DECODE(BITAND(L.JOB_STATUS
, 1+4+8+16+32+128+8192)
, 0
, 'DISABLED'
, 1
, (CASE WHEN L.RETRY_COUNT>0 THEN 'RETRY SCHEDULED' WHEN (BITAND(L.JOB_STATUS
, 1024) <> 0) THEN 'READY TO RUN' ELSE 'SCHEDULED' END)
, 4
, 'COMPLETED'
, 8
, 'BROKEN'
, 16
, 'FAILED'
, 32
, 'SUCCEEDED'
, 128
, 'REMOTE'
, 8192
, 'STOPPED'
, NULL))
, NULL
, L.RUN_COUNT
, NULL
, L.FAILURE_COUNT
, NULL
, /* RETRY_COUNT */ L.RETRY_COUNT
, /* LAST_START_DATE */ L.LAST_START_DATE
, /* LAST_RUN_DURATION*/ (CASE WHEN L.LAST_END_DATE>L.LAST_START_DATE THEN L.LAST_END_DATE-L.LAST_START_DATE ELSE NULL END)
, /* NEXT_RUN_DATE */ L.NEXT_RUN_DATE
, /* SCHEDULER_LIMIT */ NULL
, /* MAX_RUN_DURATION */ NULL
, /* LOGGING_LEVEL */ DECODE(BITAND(L.FLAGS
, 32+64+128+256)
, 32
, 'OFF'
, 64
, 'RUNS'
, 128
, 'FAILED RUNS'
, 256
, 'FULL'
, NULL)
, /* STORE_OUTPUT */ DECODE(BITAND(L.FLAGS
, 9007199254740992)
, 0
, 'FALSE'
, 'TRUE')
, /* STOP_ON_WINDOW_CLOSE */ DECODE(BITAND(L.FLAGS
, 8)
, 0
, 'FALSE'
, 'TRUE')
, /* INSTANCE_STICKINESS */ DECODE(BITAND(L.FLAGS
, 16)
, 0
, 'FALSE'
, 'TRUE')
, /* RAISE_EVENTS */ SYS.DBMS_SCHEDULER.GENERATE_EVENT_LIST(L.JOB_STATUS)
, /* SYSTEM */ DECODE(BITAND(L.FLAGS
, 16777216)
, 0
, 'FALSE'
, 'TRUE')
, /* JOB_WEIGHT */ NULL
, /* NLS_ENV */ NULL
, /* SOURCE */ NULL
, /* NUMBER_OF_DESTINATIONS */ 1
, /* DESTINATION_OWNER */ NULL
, /* DESTINATION */ NULL
, /* CREDENTIAL_OWNER */ NULL
, /* CREDENTIAL_NAME */ NULL
, /* INSTANCE_ID */ L.INSTANCE_ID
, /* DEFERRED_DROP */ DECODE(BITAND(L.JOB_STATUS
, 131072)
, 0
, 'FALSE'
, 'TRUE')
, /* ALLOW_RUNS_IN_RESTRICTED_MODE */ DECODE(BITAND(L.FLAGS
, 17179869184)
, 0
, 'FALSE'
, 'TRUE')
, /* COMMENTS */ NULL
, /* FLAGS*/ L.FLAGS
, /* RESTARTABLE */ DECODE(BITAND(L.FLAGS
, 35184372088832 + 70368744177664)
, 35184372088832 + 70368744177664
, 'TRUE'
, 'FALSE')
, NULL
, NULL
FROM SCHEDULER$_LWJOB_OBJ LO
, USER$ LU
, OBJ$ LCO
, SCHEDULER$_COMB_LW_JOB L
, OBJ$ PO
WHERE ((BITAND(L.FLAGS
, 8589934592) = 0
AND PO.TYPE# = 67) OR (BITAND(L.FLAGS
, 8589934592) <> 0
AND PO.TYPE# = 66))
AND BITAND(L.FLAGS
, 137438953472) = 0
AND L.OBJ# = LO.OBJ#
AND L.PROGRAM_OID = PO.OBJ#
AND LO.USERID = LU.USER#
AND L.CLASS_OID = LCO.OBJ#(+) AND (LO.USERID = USERENV('SCHEMAID') OR PO.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)) OR (EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-265 /* CREATE ANY JOB */
, -255 /* EXPORT FULL DATABASE */ ) )
AND LO.USERID!=0)) UNION ALL SELECT MU.NAME
, MR.NAME
, NULL
, 'IN_MEMORY_FULL'
, MD.CREATOR
, MD.CLIENT_ID
, MD.GUID
, MU.NAME
, MPO.NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, MCO.NAME
, DECODE(BITAND(MR.JOB_STATUS
, 1)
, 1
, 'TRUE'
, 'FALSE')
, 'TRUE'
, 'FALSE'
, 'FALSE'
, DECODE(BITAND(MR.JOB_STATUS
, 2+65536)
, 2
, 'RUNNING'
, 2+65536
, 'CHAIN_STALLED'
, DECODE(BITAND(MR.JOB_STATUS
, 1+4+8+16+32+128+8192)
, 0
, 'DISABLED'
, 1
, (CASE WHEN (BITAND(MR.JOB_STATUS
, 1024) <> 0) THEN 'READY TO RUN' ELSE 'SCHEDULED' END)
, 4
, 'COMPLETED'
, 8
, 'BROKEN'
, 16
, 'FAILED'
, 32
, 'SUCCEEDED'
, 128
, 'REMOTE'
, 8192
, 'STOPPED'
, NULL))
, NULL
, CAST(NULL AS NUMBER)
, MR.RUN_COUNT
, NULL
, MR.FAILURE_COUNT
, NULL
, MR.LAST_START_DATE
, NULL
, MR.NEXT_RUN_DATE
, NULL
, NULL
, DECODE(BITAND(MD.FLAGS
, 32+64+128+256)
, 32
, 'OFF'
, 64
, 'RUNS'
, 128
, 'FAILED RUNS'
, 256
, 'FULL'
, NULL)
, DECODE(BITAND(MD.FLAGS
, 9007199254740992)
, 0
, 'FALSE'
, 'TRUE')
, 'FALSE'
, 'TRUE'
, NULL
, DECODE(BITAND(MD.FLAGS
, 16777216)
, 0
, 'FALSE'
, 'TRUE')
, NULL
, NULL
, NULL
, 1
, NULL
, NULL
, NULL
, NULL
, MD.INSTANCE_ID
, 'FALSE'
, 'FALSE'
, NULL
, MD.FLAGS
, 'FALSE'
, NULL
, NULL
FROM GV$SCHEDULER_INMEM_MDINFO MD
, GV$SCHEDULER_INMEM_RTINFO MR
, OBJ$ MPO
, USER$ MU
, OBJ$ MCO
WHERE MD.OBJID = MR.OBJID
AND MPO.OBJ# = MD.PRGOID
AND MD.CLSOID = MCO.OBJ#(+)
AND MU.USER# = MR.USERID
AND MR.ID_TYPE = 2 AND (MR.USERID = USERENV('SCHEMAID') OR MPO.OBJ# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)) OR (EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-265 /* CREATE ANY JOB */
, -255 /* EXPORT FULL DATABASE */ ) )
AND MR.USERID!=0))