The database administrator can configure the thresholds using the DBMS_SERVER_ALERT package to issue alerts. However, before the database administrator does this, there is a default configuration set by Oracle. For example, if the size of the used space in the tablespace exceeds 85%, a Warning Alert is issued, while if the used space exceeds 97%, a Critical Alert is issued.

The DBMS_SERVER_ALERT package includes several procedures and functions:

• .EXPAND_MESSAGE
• .GET_THRESHOLD
• .SET_THRESHOLD
• .VIEW_THRESHOLDS

Now, let’s assume that the database administrator wants to configure the tablespace usage so that a Warning Alert is issued if the used space exceeds 60%, and a Critical Alert is issued if the used space exceeds 70%.

begin

dbms_server_alert.set_threshold(

metrics_id=>dbms_server_alert.tablespace_pct_full,

warning_operator=>dbms_server_alert.operator_ge,

warning_value=>'60',

critical_operator=>dbms_server_alert.operator_ge, critical_value=>'70',

observation_period=>1,

consecutive_occurrences=>2,

instance_name=>'ORCL',

object_type=>dbms_server_alert.object_type_tablespace,

object_name=>'USERS');

end;

Now, if the used space in the Users Tablespace exceeds 60%, a Warning Alert will be issued, and if the used space exceeds 70%, a Critical Alert will be issued. At that time, the information can be queried in DBA_OUTSTANDING_ALERTS. However, if the database administrator intervenes and increases the size of the Users Tablespace, the message will disappear from DBA_ALERT_HISTORY and will appear in DBA_OUTSTANDING_ALERTS.