Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Tablespace Thresholds and Alerts (DBMS_SERVER_ALERT)

Oracle allows you to set tablespace thresholds as an early warning mechanism for space issues. These can be set database-wide, or for individual tablespaces. When the threshold is crossed warnings are sent by the Enterprise Manager (DB Control, Grid Control or Cloud Control).

There are two types of tablespace thresholds that can be set.

The thresholds are set using the DBMS_SERVER_ALERT package, as shown in the following examples. Setting the OBJECT_NAME parameter to NULL sets the default threshold, while specifying a tablespace name sets the threshold for the specified tablespace.

BEGIN
  -- Database-wide KB free threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);
    
  -- Database-wide percent full threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '85',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '97',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);

  -- Tablespace-specific KB free threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_byt_free,
    warning_operator        => DBMS_SERVER_ALERT.operator_le,
    warning_value           => '1024000',
    critical_operator       => DBMS_SERVER_ALERT.operator_le,
    critical_value          => '102400',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
    
  -- Tablespace-specific percent full threshold.
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '90',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '98',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
END;
/

Setting the warning and critical levels to '0' disables the notification.

The threshold settings can be displayed using the DBA_THRESHOLDS view.

SET LINESIZE 200

COLUMN tablespace_name FORMAT A30
COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15

SELECT object_name AS tablespace_name,
       metrics_name,
       warning_operator,
       warning_value,
       critical_operator,
       critical_value
FROM   dba_thresholds
WHERE  object_type = 'TABLESPACE'
ORDER BY object_name;

TABLESPACE_NAME                METRICS_NAME                   WARNING_OPER WARNING_VALUE                  CRITICAL_OPE CRITICAL_VALUE
------------------------------ ------------------------------ ------------ ------------------------------ ------------ ---------------
TEMP                           Tablespace Space Usage         DO NOT CHECK <SYSTEM-GENERATED THRESHOLD>   DO_NOT_CHECK 0
UNDOTBS1                       Tablespace Space Usage         DO NOT CHECK <SYSTEM-GENERATED THRESHOLD>   DO_NOT_CHECK 0
UNDOTBS2                       Tablespace Space Usage         DO NOT CHECK <SYSTEM-GENERATED THRESHOLD>   DO_NOT_CHECK 0
USERS                          Tablespace Bytes Space Usage   LE           1024000                        LE           102400
USERS                          Tablespace Space Usage         GE           90                             GE           98
                               Tablespace Space Usage         GE           85                             GE           97
                               Tablespace Bytes Space Usage   LE           1024000                        LE           102400

7 rows selected.

SQL>

Oracle 11g Release 2 introduced the DBA_TABLESPACE_THRESHOLDS view, which displays the settings for all tablespaces, showing the default where no tablespace-specific threshold is set.

SET LINESIZE 200

COLUMN metrics_name FORMAT A30
COLUMN warning_value FORMAT A30
COLUMN critical_value FORMAT A15

SELECT tablespace_name,
       contents,
       extent_management,
       threshold_type,
       metrics_name,
       warning_operator,
       warning_value,
       critical_operator,
       critical_value
FROM   dba_tablespace_thresholds
ORDER BY tablespace_name;

TABLESPACE_NAME                CONTENTS  EXTENT_MAN THRESHOL METRICS_NAME                   WARNING_OPER WARNING_VALUE                  CRITICAL_OPE CRITICAL_VALUE
------------------------------ --------- ---------- -------- ------------------------------ ------------ ------------------------------ ------------ ---------------
EXAMPLE                        PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
EXAMPLE                        PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSAUX                         PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
SYSAUX                         PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSTEM                         PERMANENT LOCAL      DEFAULT  Tablespace Bytes Space Usage   LE           1024000                        LE           102400
SYSTEM                         PERMANENT LOCAL      DEFAULT  Tablespace Space Usage         GE           85                             GE           97
TEMP                           TEMPORARY LOCAL      EXPLICIT Tablespace Space Usage         DO NOT CHECK <SYSTEM-GENERATED THRESHOLD>   DO NOT CHECK 0
UNDOTBS1                       UNDO      LOCAL      EXPLICIT Tablespace Space Usage         DO NOT CHECK <SYSTEM-GENERATED THRESHOLD>   DO NOT CHECK 0
USERS                          PERMANENT LOCAL      EXPLICIT Tablespace Bytes Space Usage   LE           1024000                        LE           102400
USERS                          PERMANENT LOCAL      EXPLICIT Tablespace Space Usage         GE           90                             GE           98

10 rows selected.

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.