VIEW: SYS.DBA_TABLESPACE_THRESHOLDS

Source

View Text - Preformatted

SELECT tablespace_name,
         contents,
         extent_management,
         decode(threshold_type, 1, 'EXPLICIT',
                                2, 'DEFAULT',
                                'NONE') as threshold_type,
         metrics_name,
         decode(warning_operator, 0, 'GT',
                                  1, 'EQ',
                                  2, 'LT',
                                  3, 'LE',
                                  4, 'GE',
                                  5, 'CONTAINS',
                                  6, 'NE',
                                  7, 'DO NOT CHECK',
                                  'NONE') AS warning_operator,
         warning_value,
         decode(critical_operator, 0, 'GT',
                                   1, 'EQ',
                                   2, 'LT',
                                   3, 'LE',
                                   4, 'GE',
                                   5, 'CONTAINS',
                                   6, 'NE',
                                   7, 'DO NOT CHECK',
                                   'NONE') AS critical_operator,
         critical_value
    FROM
    (SELECT tbs.tablespace_name,
            tbs.contents,
            tbs.extent_management,
            decode(m.metrics_name, NULL, 0, 1) as threshold_type,
            m.metrics_name,
            m.warning_operator,
            m.warning_value,
            m.critical_operator,
            m.critical_value
      FROM
         ((SELECT tablespace_name,
                 contents,
                 extent_management
             FROM DBA_TABLESPACES
            WHERE tablespace_name IN
                  (SELECT object_name
                     FROM table(dbms_server_alert.view_thresholds)
                    WHERE object_type = 5
                      AND object_name IS NOT NULL
                      AND metrics_id IN (9000, 9001))
          ) tbs
       LEFT OUTER JOIN
         (SELECT a.object_name,
                m.name AS metrics_name,
                a.warning_operator AS warning_operator,
                a.warning_value AS warning_value,
                a.critical_operator AS critical_operator,
                a.critical_value AS critical_value,
                c.contents
            FROM table(dbms_server_alert.view_thresholds) a,
                 X$KEWMDSM m,
                 (SELECT 'PERMANENT' AS contents FROM DUAL
                   WHERE EXISTS (SELECT * FROM GV$SYSTEM_PARAMETER3
                                  WHERE name = '_enable_tablespace_alerts'
                                    AND display_value='TRUE')
                  UNION ALL
                  SELECT 'TEMPORARY' FROM DUAL
                   WHERE EXISTS (SELECT * FROM GV$SYSTEM_PARAMETER3
                                  WHERE name = '_enable_tablespace_alerts'
                                    AND display_value = 'TRUE')
                     AND EXISTS (SELECT * FROM GV$SYSTEM_PARAMETER3
                                 WHERE name = '_disable_temp_tablespace_alerts'
                                    AND display_value = 'FALSE')
                  UNION ALL
                  SELECT 'UNDO' FROM DUAL
                   WHERE EXISTS (SELECT * FROM GV$SYSTEM_PARAMETER3
                                  WHERE name = '_enable_tablespace_alerts'
                                          AND display_value='TRUE')
                     AND EXISTS (SELECT * FROM GV$SYSTEM_PARAMETER3
                                 WHERE name = '_disable_undo_tablespace_alerts'
                                    AND display_value = 'FALSE')) c
           WHERE a.object_type = 5
             AND a.flags = 1
             AND a.object_name IS NOT NULL
             AND a.metrics_id IN (9000, 9001)
             AND m.metricid = a.metrics_id) m
        ON (tbs.tablespace_name = m.object_name
            and tbs.contents = m.contents))
   UNION ALL
    SELECT tbs.tablespace_name,
            tbs.contents,
            tbs.extent_management,
            decode(m.metrics_name, NULL, 0, 2) as threshold_type,
            m.metrics_name,
            m.warning_operator,
            m.warning_value,
            m.critical_operator,
            m.critical_value
      FROM
        ((SELECT tablespace_name,
                 contents,
                 extent_management
            FROM DBA_TABLESPACES
           WHERE tablespace_name NOT IN
                  (SELECT object_name
                     FROM table(dbms_server_alert.view_thresholds)
                    WHERE object_type = 5
                      AND object_name IS NOT NULL
                      AND metrics_id IN (9000, 9001))
         ) tbs
         LEFT OUTER JOIN
         (SELECT 'LOCAL' as extent_management,
                  m.name AS metrics_name,
                  a.warning_operator AS warning_operator,
                  a.warning_value AS warning_value,
                  a.critical_operator AS critical_operator,
                  a.critical_value AS critical_value,
                  c.contents
            FROM table(dbms_server_alert.view_thresholds) a,
                 X$KEWMDSM m,
                 (SELECT 'PERMANENT' AS contents FROM DUAL
                   WHERE EXISTS (SELECT * FROM GV$SYSTEM_PARAMETER3
                                  WHERE name = '_enable_tablespace_alerts'
                                    AND display_value='TRUE')
                  UNION ALL
                  SELECT 'TEMPORARY' FROM DUAL
                   WHERE EXISTS (SELECT * FROM GV$SYSTEM_PARAMETER3
                                  WHERE name = '_enable_tablespace_alerts'
                                    AND display_value = 'TRUE')
                     AND EXISTS (SELECT * FROM GV$SYSTEM_PARAMETER3
                                 WHERE name = '_disable_temp_tablespace_alerts'
                                   AND display_value = 'FALSE')
                     AND EXISTS (SELECT * FROM GV$SYSTEM_PARAMETER3
                                  WHERE name = '_enable_default_temp_threshold'
                                    AND display_value = 'TRUE')
                  UNION ALL
                  SELECT 'UNDO' FROM DUAL
                   WHERE EXISTS (SELECT * FROM GV$SYSTEM_PARAMETER3
                                  WHERE name = '_enable_tablespace_alerts'
                                          AND display_value='TRUE')
                     AND EXISTS (SELECT * FROM GV$SYSTEM_PARAMETER3
                                 WHERE name = '_disable_undo_tablespace_alerts'
                                   AND display_value = 'FALSE')
                     AND EXISTS (SELECT * FROM GV$SYSTEM_PARAMETER3
                                  WHERE name = '_enable_default_undo_threshold'
                                    AND display_value = 'TRUE')) c
           WHERE a.object_type = 5
             AND a.object_name IS NULL
             AND a.metrics_id IN (9000, 9001)
             AND m.metricid = a.metrics_id) m
        ON (tbs.extent_management =  m.extent_management
            and tbs.contents = m.contents)))

View Text - HTML Formatted

SELECT TABLESPACE_NAME
, CONTENTS
, EXTENT_MANAGEMENT
, DECODE(THRESHOLD_TYPE
, 1
, 'EXPLICIT'
, 2
, 'DEFAULT'
, 'NONE') AS THRESHOLD_TYPE
, METRICS_NAME
, DECODE(WARNING_OPERATOR
, 0
, 'GT'
, 1
, 'EQ'
, 2
, 'LT'
, 3
, 'LE'
, 4
, 'GE'
, 5
, 'CONTAINS'
, 6
, 'NE'
, 7
, 'DO NOT CHECK'
, 'NONE') AS WARNING_OPERATOR
, WARNING_VALUE
, DECODE(CRITICAL_OPERATOR
, 0
, 'GT'
, 1
, 'EQ'
, 2
, 'LT'
, 3
, 'LE'
, 4
, 'GE'
, 5
, 'CONTAINS'
, 6
, 'NE'
, 7
, 'DO NOT CHECK'
, 'NONE') AS CRITICAL_OPERATOR
, CRITICAL_VALUE FROM (SELECT TBS.TABLESPACE_NAME
, TBS.CONTENTS
, TBS.EXTENT_MANAGEMENT
, DECODE(M.METRICS_NAME
, NULL
, 0
, 1) AS THRESHOLD_TYPE
, M.METRICS_NAME
, M.WARNING_OPERATOR
, M.WARNING_VALUE
, M.CRITICAL_OPERATOR
, M.CRITICAL_VALUE FROM ((SELECT TABLESPACE_NAME
, CONTENTS
, EXTENT_MANAGEMENT
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME IN (SELECT OBJECT_NAME
FROM TABLE(DBMS_SERVER_ALERT.VIEW_THRESHOLDS)
WHERE OBJECT_TYPE = 5
AND OBJECT_NAME IS NOT NULL
AND METRICS_ID IN (9000
, 9001)) ) TBS LEFT OUTER JOIN (SELECT A.OBJECT_NAME
, M.NAME AS METRICS_NAME
, A.WARNING_OPERATOR AS WARNING_OPERATOR
, A.WARNING_VALUE AS WARNING_VALUE
, A.CRITICAL_OPERATOR AS CRITICAL_OPERATOR
, A.CRITICAL_VALUE AS CRITICAL_VALUE
, C.CONTENTS
FROM TABLE(DBMS_SERVER_ALERT.VIEW_THRESHOLDS) A
, X$KEWMDSM M
, (SELECT 'PERMANENT' AS CONTENTS
FROM DUAL
WHERE EXISTS (SELECT *
FROM GV$SYSTEM_PARAMETER3
WHERE NAME = '_ENABLE_TABLESPACE_ALERTS'
AND DISPLAY_VALUE='TRUE') UNION ALL SELECT 'TEMPORARY'
FROM DUAL
WHERE EXISTS (SELECT *
FROM GV$SYSTEM_PARAMETER3
WHERE NAME = '_ENABLE_TABLESPACE_ALERTS'
AND DISPLAY_VALUE = 'TRUE')
AND EXISTS (SELECT *
FROM GV$SYSTEM_PARAMETER3
WHERE NAME = '_DISABLE_TEMP_TABLESPACE_ALERTS'
AND DISPLAY_VALUE = 'FALSE') UNION ALL SELECT 'UNDO'
FROM DUAL
WHERE EXISTS (SELECT *
FROM GV$SYSTEM_PARAMETER3
WHERE NAME = '_ENABLE_TABLESPACE_ALERTS'
AND DISPLAY_VALUE='TRUE')
AND EXISTS (SELECT *
FROM GV$SYSTEM_PARAMETER3
WHERE NAME = '_DISABLE_UNDO_TABLESPACE_ALERTS'
AND DISPLAY_VALUE = 'FALSE')) C
WHERE A.OBJECT_TYPE = 5
AND A.FLAGS = 1
AND A.OBJECT_NAME IS NOT NULL
AND A.METRICS_ID IN (9000
, 9001)
AND M.METRICID = A.METRICS_ID) M ON (TBS.TABLESPACE_NAME = M.OBJECT_NAME
AND TBS.CONTENTS = M.CONTENTS)) UNION ALL SELECT TBS.TABLESPACE_NAME
, TBS.CONTENTS
, TBS.EXTENT_MANAGEMENT
, DECODE(M.METRICS_NAME
, NULL
, 0
, 2) AS THRESHOLD_TYPE
, M.METRICS_NAME
, M.WARNING_OPERATOR
, M.WARNING_VALUE
, M.CRITICAL_OPERATOR
, M.CRITICAL_VALUE FROM ((SELECT TABLESPACE_NAME
, CONTENTS
, EXTENT_MANAGEMENT
FROM DBA_TABLESPACES
WHERE TABLESPACE_NAME NOT IN (SELECT OBJECT_NAME
FROM TABLE(DBMS_SERVER_ALERT.VIEW_THRESHOLDS)
WHERE OBJECT_TYPE = 5
AND OBJECT_NAME IS NOT NULL
AND METRICS_ID IN (9000
, 9001)) ) TBS LEFT OUTER JOIN (SELECT 'LOCAL' AS EXTENT_MANAGEMENT
, M.NAME AS METRICS_NAME
, A.WARNING_OPERATOR AS WARNING_OPERATOR
, A.WARNING_VALUE AS WARNING_VALUE
, A.CRITICAL_OPERATOR AS CRITICAL_OPERATOR
, A.CRITICAL_VALUE AS CRITICAL_VALUE
, C.CONTENTS
FROM TABLE(DBMS_SERVER_ALERT.VIEW_THRESHOLDS) A
, X$KEWMDSM M
, (SELECT 'PERMANENT' AS CONTENTS
FROM DUAL
WHERE EXISTS (SELECT *
FROM GV$SYSTEM_PARAMETER3
WHERE NAME = '_ENABLE_TABLESPACE_ALERTS'
AND DISPLAY_VALUE='TRUE') UNION ALL SELECT 'TEMPORARY'
FROM DUAL
WHERE EXISTS (SELECT *
FROM GV$SYSTEM_PARAMETER3
WHERE NAME = '_ENABLE_TABLESPACE_ALERTS'
AND DISPLAY_VALUE = 'TRUE')
AND EXISTS (SELECT *
FROM GV$SYSTEM_PARAMETER3
WHERE NAME = '_DISABLE_TEMP_TABLESPACE_ALERTS'
AND DISPLAY_VALUE = 'FALSE')
AND EXISTS (SELECT *
FROM GV$SYSTEM_PARAMETER3
WHERE NAME = '_ENABLE_DEFAULT_TEMP_THRESHOLD'
AND DISPLAY_VALUE = 'TRUE') UNION ALL SELECT 'UNDO'
FROM DUAL
WHERE EXISTS (SELECT *
FROM GV$SYSTEM_PARAMETER3
WHERE NAME = '_ENABLE_TABLESPACE_ALERTS'
AND DISPLAY_VALUE='TRUE')
AND EXISTS (SELECT *
FROM GV$SYSTEM_PARAMETER3
WHERE NAME = '_DISABLE_UNDO_TABLESPACE_ALERTS'
AND DISPLAY_VALUE = 'FALSE')
AND EXISTS (SELECT *
FROM GV$SYSTEM_PARAMETER3
WHERE NAME = '_ENABLE_DEFAULT_UNDO_THRESHOLD'
AND DISPLAY_VALUE = 'TRUE')) C
WHERE A.OBJECT_TYPE = 5
AND A.OBJECT_NAME IS NULL
AND A.METRICS_ID IN (9000
, 9001)
AND M.METRICID = A.METRICS_ID) M ON (TBS.EXTENT_MANAGEMENT = M.EXTENT_MANAGEMENT
AND TBS.CONTENTS = M.CONTENTS)))