Setting Tablespace Thresholds and Alerts

How to set tablespace thresholds?

If you have very less no. of targets and you are using dbcontrol for each database, then use DBMS_SERVER_ALERT package to set. If you have OEM Cloud control 12c / 13c, then you can create a new Metric extension and and Rule and apply it. Here we ll discuss how to use DBMS_SERVER_ALERT package.

Use of the DBMS_SERVER_ALERT package as an early warning mechanism for space issues. The DBMS_SERVER_ALERT package 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).

Setting the OBJECT_NAME parameter to NULL sets the default threshold for all tablespace in the database. Setting the OBJECT_NAME parameter to a tablespace name sets the threshold for the specified tablespace and overrides any default setting.

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

TABLESPACE_PCT_FULL : Percent full. 

When the warning or critical threshold based on percent full is crossed a notification occurs.

TABLESPACE_BYT_FREE : Free Space Remaining (KB). 

The constant name implies the value is in bytes, but it is specified in KB. When the warning or critical threshold based on remaining free space is crossed a notification occurs. When you view these thresholds in different tools the units may vary, for example

Cloud Control displays and sets these values in MB.
The thresholds are set using a value and an operator.

OPERATOR_LE : Less than or equal.
OPERATOR_GE : Greater than or equal.


Setting Thresholds:

Note:  You should know of your existing thresholds before changing them, so you know what to set them back to.

The following examples show how to set the different types of alerts.

Example-1:  Database-wide KB free threshold.

Begin
  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);
end;
/

Example-2:    Database-wide percent full threshold.

Begin
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '80',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '90',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => NULL);
end;
/

Example-3:  Tablespace-specific KB free threshold.

begin
  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');
end;
/

Example-4:    Tablespace-specific percent full threshold.

begin
  DBMS_SERVER_ALERT.set_threshold(
    metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
    warning_operator        => DBMS_SERVER_ALERT.operator_ge,
    warning_value           => '80',
    critical_operator       => DBMS_SERVER_ALERT.operator_ge,
    critical_value          => '90',
    observation_period      => 1,
    consecutive_occurrences => 1,
    instance_name           => NULL,
    object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
    object_name             => 'USERS');
end;
/

Example-5: Tablespace-specific reset to defaults ( Set warning and critical values to NULL)

  --DBMS_SERVER_ALERT.set_threshold(
  --  metrics_id              => DBMS_SERVER_ALERT.tablespace_pct_full,
  --  warning_operator        => DBMS_SERVER_ALERT.operator_ge,
  --  warning_value           => NULL,
  --  critical_operator       => DBMS_SERVER_ALERT.operator_ge,
  --  critical_value          => NULL,
  --  observation_period      => 1,
  --  consecutive_occurrences => 1,
  --  instance_name           => NULL,
  --  object_type             => DBMS_SERVER_ALERT.object_type_tablespace,
  --  object_name             => 'USERS');


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

Displaying Thresholds
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;

Ref:
https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_salt.htm#CHDIFIHI