VIEW: SYS.DBA_AUTOTASK_WINDOW_CLIENTS

Source

View Text - Preformatted

SELECT "WINDOW_NAME","WINDOW_NEXT_TIME","WINDOW_ACTIVE","AUTOTASK_STATUS","OPTIMIZER_STATS","SEGMENT_ADVISOR","SQL_TUNE_ADVISOR","HEALTH_MONITOR" FROM (
     WITH L AS (SELECT X.CID_KETCL AS CLIENT_ID,
                       G.WINDOW_NAME
                  FROM DBA_SCHEDULER_WINGROUP_MEMBERS G,
                       X$KETCL X,
                       KET$_CLIENT_CONFIG CC
                  WHERE G.WINDOW_GROUP_NAME = X.WGRP_KETCL
                    AND X.CID_KETCL = CC.CLIENT_ID
                    AND CC.OPERATION_ID = 0
                    AND BITAND(X.ATTR_KETCL, 2048) = 0
                    AND CC.STATUS = 2
                    AND dbms_auto_task.get_client_status_override(
                          CC.CLIENT_ID) = 0) ,
          M AS (SELECT W.WINDOW_NAME,
                       W.NEXT_START_DATE,
                       W.ACTIVE
                  FROM DBA_SCHEDULER_WINDOWS W,
                       DBA_SCHEDULER_WINGROUP_MEMBERS G
                 WHERE W.ENABLED = 'TRUE'
                   AND W.WINDOW_NAME = G.WINDOW_NAME
                   AND G.WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP')
SELECT M.WINDOW_NAME WINDOW_NAME,
       M.NEXT_START_DATE WINDOW_NEXT_TIME,
       M.ACTIVE WINDOW_ACTIVE,
       DECODE((SELECT COUNT(*) FROM L WHERE CLIENT_ID = 0
                  AND L.WINDOW_NAME = M.WINDOW_NAME),
              0, 'DISABLED', 'ENABLED') AS AUTOTASK_STATUS,
       DECODE((SELECT COUNT(*) FROM L WHERE CLIENT_ID = 4
                  AND L.WINDOW_NAME = M.WINDOW_NAME),
              0, 'DISABLED', 'ENABLED') AS OPTIMIZER_STATS,
       DECODE((SELECT COUNT(*) FROM L WHERE CLIENT_ID = 5
                  AND L.WINDOW_NAME = M.WINDOW_NAME),
              0, 'DISABLED', 'ENABLED') AS SEGMENT_ADVISOR,
--     DECODE((SELECT COUNT(*) FROM L WHERE CLIENT_ID = 3
--                AND L.WINDOW_NAME = M.WINDOW_NAME),
--            0, 'DISABLED', 'ENABLED') AS SEGMENT_SHRINK,
       DECODE((SELECT COUNT(*) FROM L WHERE CLIENT_ID = 6
                  AND L.WINDOW_NAME = M.WINDOW_NAME),
              0, 'DISABLED', 'ENABLED') AS SQL_TUNE_ADVISOR,
       DECODE((SELECT COUNT(*) FROM L WHERE CLIENT_ID = 7
                  AND L.WINDOW_NAME = M.WINDOW_NAME),
              0, 'DISABLED', 'ENABLED') AS HEALTH_MONITOR
--    , DECODE((SELECT COUNT(*) FROM L WHERE CLIENT_ID = 6
--                AND L.WINDOW_NAME = M.WINDOW_NAME),
--            0, 'DISABLED', 'ENABLED') AS ONLINE_BACKUP
  FROM M)

View Text - HTML Formatted

SELECT "WINDOW_NAME"
, "WINDOW_NEXT_TIME"
, "WINDOW_ACTIVE"
, "AUTOTASK_STATUS"
, "OPTIMIZER_STATS"
, "SEGMENT_ADVISOR"
, "SQL_TUNE_ADVISOR"
, "HEALTH_MONITOR"
FROM ( WITH L AS (SELECT X.CID_KETCL AS CLIENT_ID
, G.WINDOW_NAME
FROM DBA_SCHEDULER_WINGROUP_MEMBERS G
, X$KETCL X
, KET$_CLIENT_CONFIG CC
WHERE G.WINDOW_GROUP_NAME = X.WGRP_KETCL
AND X.CID_KETCL = CC.CLIENT_ID
AND CC.OPERATION_ID = 0
AND BITAND(X.ATTR_KETCL
, 2048) = 0
AND CC.STATUS = 2
AND DBMS_AUTO_TASK.GET_CLIENT_STATUS_OVERRIDE( CC.CLIENT_ID) = 0)
, M AS (SELECT W.WINDOW_NAME
, W.NEXT_START_DATE
, W.ACTIVE
FROM DBA_SCHEDULER_WINDOWS W
, DBA_SCHEDULER_WINGROUP_MEMBERS G
WHERE W.ENABLED = 'TRUE'
AND W.WINDOW_NAME = G.WINDOW_NAME
AND G.WINDOW_GROUP_NAME = 'MAINTENANCE_WINDOW_GROUP') SELECT M.WINDOW_NAME WINDOW_NAME
, M.NEXT_START_DATE WINDOW_NEXT_TIME
, M.ACTIVE WINDOW_ACTIVE
, DECODE((SELECT COUNT(*)
FROM L
WHERE CLIENT_ID = 0
AND L.WINDOW_NAME = M.WINDOW_NAME)
, 0
, 'DISABLED'
, 'ENABLED') AS AUTOTASK_STATUS
, DECODE((SELECT COUNT(*)
FROM L
WHERE CLIENT_ID = 4
AND L.WINDOW_NAME = M.WINDOW_NAME)
, 0
, 'DISABLED'
, 'ENABLED') AS OPTIMIZER_STATS
, DECODE((SELECT COUNT(*)
FROM L
WHERE CLIENT_ID = 5
AND L.WINDOW_NAME = M.WINDOW_NAME)
, 0
, 'DISABLED'
, 'ENABLED') AS SEGMENT_ADVISOR
, -- DECODE((SELECT COUNT(*)
FROM L
WHERE CLIENT_ID = 3 --
AND L.WINDOW_NAME = M.WINDOW_NAME)
, -- 0
, 'DISABLED'
, 'ENABLED') AS SEGMENT_SHRINK
, DECODE((SELECT COUNT(*)
FROM L
WHERE CLIENT_ID = 6
AND L.WINDOW_NAME = M.WINDOW_NAME)
, 0
, 'DISABLED'
, 'ENABLED') AS SQL_TUNE_ADVISOR
, DECODE((SELECT COUNT(*)
FROM L
WHERE CLIENT_ID = 7
AND L.WINDOW_NAME = M.WINDOW_NAME)
, 0
, 'DISABLED'
, 'ENABLED') AS HEALTH_MONITOR --
, DECODE((SELECT COUNT(*)
FROM L
WHERE CLIENT_ID = 6 --
AND L.WINDOW_NAME = M.WINDOW_NAME)
, -- 0
, 'DISABLED'
, 'ENABLED') AS ONLINE_BACKUP
FROM M)