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 dba_scheduler_job_dests djd
where djd.owner = ju.name and djd.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, obj$ co, sys.scheduler$_job j,
(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'))
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 2^45 */
DECODE(BITAND(l.flags, 35184372088832),0,'FALSE','TRUE'),
/* RESTART_ON_FAILURE, 2^46*/
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,
l.retry_count, l.last_start_date,
(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#(+)
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
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 DBA_SCHEDULER_JOB_DESTS DJD
WHERE DJD.OWNER = JU.NAME
AND DJD.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
, OBJ$ CO
, SYS.SCHEDULER$_JOB J
,
(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'))
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 2^45 */
DECODE(BITAND(L.FLAGS
, 35184372088832)
, 0
, 'FALSE'
, 'TRUE')
,
/* RESTART_ON_FAILURE
, 2^46*/
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
,
L.RETRY_COUNT
, L.LAST_START_DATE
,
(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#(+)
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
|
|
|