[Home] [Help]
SELECT
C.CNAME_KETCL,
O.PRG_KETOP,
TG.TNAME_KETTG,
T.TARGET_NAME,
O.OPNAME_KETOP,
dbms_auto_task.decode_attributes(T.ATTRIBUTES),
T.TASK_PRIORITY,
T.PRIORITY_OVERRIDE,
DECODE(T.STATUS,1, 'DISABLED',2,'ENABLED',13,'DEFERRED','INVALID'),
T.WINDOW_NAME,
T.CURR_JOB_NAME,
SJ.STATE,
DECODE(T.EST_TYPE, 1, 'DERIVED', 2, 'FORCED', 3, 'LOCKED', 'N/A'),
T.EST_WEIGHT,
T.EST_DURATION,
T.EST_CPU_TIME,
T.EST_TEMP,
T.EST_DOP,
T.EST_IO_RATE,
T.EST_UNDO_RATE,
T.RETRY_COUNT,
T.LG_DATE,
T.LG_PRIORITY,
T.LG_DURATION,
T.LG_CPU_TIME,
T.LG_TEMP,
T.LG_DOP,
T.LG_IO_RATE,
T.LG_UNDO_RATE,
T.LG_CPU_WAIT,
T.LG_IO_WAIT,
T.LG_UNDO_WAIT,
T.LG_TEMP_WAIT,
T.LG_CONCURRENCY,
T.LG_CONTENTION,
W.NEXT_START_DATE,
T.LT_DATE,
T.LT_PRIORITY,
CASE T.LT_TERM_CODE
WHEN NULL THEN 'N/A'
WHEN 10 THEN 'SUCCEEDED'
WHEN 11 THEN 'FAILED'
WHEN 12 THEN 'STOPPED BY USER ACTION'
WHEN 13 THEN 'STOPPED AT END OF MAINTENANCE WINDOW'
WHEN 14 THEN 'STOPPED AT INSTANCE SHUTDOWN'
WHEN 15 THEN 'STOPPED'
ELSE 'UNKNOWN'
END,
T.LT_DURATION,
T.LT_CPU_TIME,
T.LT_TEMP,
T.LT_DOP,
T.LT_IO_RATE,
T.LT_UNDO_RATE,
T.LT_CPU_WAIT,
T.LT_IO_WAIT,
T.LT_UNDO_WAIT,
T.LT_TEMP_WAIT,
T.LT_CONCURRENCY,
T.LT_CONTENTION,
T.MG_DURATION,
T.MG_CPU_TIME,
T.MG_TEMP,
T.MG_DOP,
T.MG_IO_RATE,
T.MG_UNDO_RATE,
T.MG_CPU_WAIT,
T.MG_IO_WAIT,
T.MG_UNDO_WAIT,
T.MG_TEMP_WAIT,
T.MG_CONCURRENCY,
T.MG_CONTENTION,
T.INFO_FIELD_1,
T.INFO_FIELD_2,
T.INFO_FIELD_3,
T.INFO_FIELD_4
FROM KET$_CLIENT_TASKS T, X$KETCL C, X$KETOP O, X$KETTG TG,
DBA_SCHEDULER_WINDOWS W, DBA_SCHEDULER_JOBS SJ
WHERE T.CLIENT_ID = C.CID_KETCL
AND (BITAND(C.ATTR_KETCL,2048) = 0
OR 999999 < (SELECT TO_NUMBER(VALUE)
FROM V$SYSTEM_PARAMETER
WHERE NAME = '_automatic_maintenance_test'))
AND C.CID_KETCL > 0
AND T.CLIENT_ID = O.CID_KETOP
AND T.OPERATION_ID = O.OPID_KETOP
AND T.TARGET_TYPE = TG.TID_KETTG
AND T.WINDOW_NAME = W.WINDOW_NAME(+)
AND T.CURR_JOB_NAME = SJ.JOB_NAME(+)
SELECT
C.CNAME_KETCL
,
O.PRG_KETOP
,
TG.TNAME_KETTG
,
T.TARGET_NAME
,
O.OPNAME_KETOP
,
DBMS_AUTO_TASK.DECODE_ATTRIBUTES(T.ATTRIBUTES)
,
T.TASK_PRIORITY
,
T.PRIORITY_OVERRIDE
,
DECODE(T.STATUS
, 1
, 'DISABLED'
, 2
, 'ENABLED'
, 13
, 'DEFERRED'
, 'INVALID')
,
T.WINDOW_NAME
,
T.CURR_JOB_NAME
,
SJ.STATE
,
DECODE(T.EST_TYPE
, 1
, 'DERIVED'
, 2
, 'FORCED'
, 3
, 'LOCKED'
, 'N/A')
,
T.EST_WEIGHT
,
T.EST_DURATION
,
T.EST_CPU_TIME
,
T.EST_TEMP
,
T.EST_DOP
,
T.EST_IO_RATE
,
T.EST_UNDO_RATE
,
T.RETRY_COUNT
,
T.LG_DATE
,
T.LG_PRIORITY
,
T.LG_DURATION
,
T.LG_CPU_TIME
,
T.LG_TEMP
,
T.LG_DOP
,
T.LG_IO_RATE
,
T.LG_UNDO_RATE
,
T.LG_CPU_WAIT
,
T.LG_IO_WAIT
,
T.LG_UNDO_WAIT
,
T.LG_TEMP_WAIT
,
T.LG_CONCURRENCY
,
T.LG_CONTENTION
,
W.NEXT_START_DATE
,
T.LT_DATE
,
T.LT_PRIORITY
,
CASE T.LT_TERM_CODE
WHEN NULL THEN 'N/A'
WHEN 10 THEN 'SUCCEEDED'
WHEN 11 THEN 'FAILED'
WHEN 12 THEN 'STOPPED BY USER ACTION'
WHEN 13 THEN 'STOPPED AT END OF MAINTENANCE WINDOW'
WHEN 14 THEN 'STOPPED AT INSTANCE SHUTDOWN'
WHEN 15 THEN 'STOPPED'
ELSE 'UNKNOWN'
END
,
T.LT_DURATION
,
T.LT_CPU_TIME
,
T.LT_TEMP
,
T.LT_DOP
,
T.LT_IO_RATE
,
T.LT_UNDO_RATE
,
T.LT_CPU_WAIT
,
T.LT_IO_WAIT
,
T.LT_UNDO_WAIT
,
T.LT_TEMP_WAIT
,
T.LT_CONCURRENCY
,
T.LT_CONTENTION
,
T.MG_DURATION
,
T.MG_CPU_TIME
,
T.MG_TEMP
,
T.MG_DOP
,
T.MG_IO_RATE
,
T.MG_UNDO_RATE
,
T.MG_CPU_WAIT
,
T.MG_IO_WAIT
,
T.MG_UNDO_WAIT
,
T.MG_TEMP_WAIT
,
T.MG_CONCURRENCY
,
T.MG_CONTENTION
,
T.INFO_FIELD_1
,
T.INFO_FIELD_2
,
T.INFO_FIELD_3
,
T.INFO_FIELD_4
FROM KET$_CLIENT_TASKS T
, X$KETCL C
, X$KETOP O
, X$KETTG TG
,
DBA_SCHEDULER_WINDOWS W
, DBA_SCHEDULER_JOBS SJ
WHERE T.CLIENT_ID = C.CID_KETCL
AND (BITAND(C.ATTR_KETCL
, 2048) = 0
OR 999999 < (SELECT TO_NUMBER(VALUE)
FROM V$SYSTEM_PARAMETER
WHERE NAME = '_AUTOMATIC_MAINTENANCE_TEST'))
AND C.CID_KETCL > 0
AND T.CLIENT_ID = O.CID_KETOP
AND T.OPERATION_ID = O.OPID_KETOP
AND T.TARGET_TYPE = TG.TID_KETTG
AND T.WINDOW_NAME = W.WINDOW_NAME(+)
AND T.CURR_JOB_NAME = SJ.JOB_NAME(+)
|
|
|
|