How to Set Alert Thresholds

Setting Alert Thresholds

For each tablespace, you can set just percent-full thresholds, just free-space-remaining thresholds, or both types of thresholds simultaneously. Setting either type of threshold to zero disables it.

The ideal setting for the warning threshold is one that issues an alert early enough for you to resolve the problem before it becomes critical. The critical threshold should be one that issues an alert still early enough so that you can take immediate action to avoid loss of service.

To set alert threshold values:

• For locally managed tablespaces, use Enterprise Manager (see Oracle Database 2 Day DBA for instructions) or the DBMS_SERVER_ALERT.SET_THRESHOLD package procedure (see Oracle Database PL/SQL Packages and Types Reference for usage details).

• For dictionary managed tablespaces, use Enterprise Manager. See Oracle Database 2 Day DBA for instructions.

Example—Locally Managed Tablespace

The following example sets the free-space-remaining thresholds in the USERS tablespace to 10 MB (warning) and 2 MB (critical), and disables the percent-full thresholds.

BEGIN
DBMS_SERVER_ALERT.SET_THRESHOLD(
<wbr>metrics_id              =&gt; DBMS_SERVER_ALERT.TABLESPACE_BYT_FREE,
   warning_operator        =&gt; DBMS_SERVER_ALERT.OPERATOR_LE,<br><wbr>warning_value           =&gt; '10240',<br><wbr>critical_operator       =&gt; DBMS_SERVER_ALERT.OPERATOR_LE,
<br>
   critical_value          =&gt; '2048',
   <br>
observation_period      =&gt; 1,
<br>
   consecutive_occurrences =&gt; 1,
<br>
   instance_name           =&gt; NULL,
   <br>
object_type             =&gt; DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
<br>
   object_name             =&gt; 'USERS');
<br>
DBMS_SERVER_ALERT.SET_THRESHOLD(
<br>
   metrics_id              =&gt; DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,<br><wbr>warning_operator        =&gt; DBMS_SERVER_ALERT.OPERATOR_GT,
   <br>
warning_value           =&gt; '0',
<br>
   critical_operator       =&gt; DBMS_SERVER_ALERT.OPERATOR_GT,<br><wbr>critical_value          =&gt; '0',<br><wbr>observation_period      =&gt; 1,<br><wbr>consecutive_occurrences =&gt; 1,<br><wbr>instance_name           =&gt; NULL,
   object_type             =&gt; DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE,
<br>
   object_name             =&gt; 'USERS');<br><wbr>END;
<br>
/
</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

Note:

When setting non-zero values for percent-full thresholds, use the greater-than-or-equal-to operator, OPERATOR_GE.

Restoring a Tablespace to Database Default Thresholds

After explicitly setting values for locally managed tablespace alert thresholds, you can cause the values to revert to the database defaults by setting them to NULL with DBMS_SERVER_ALERT.SET_THRESHOLD.

Modifying Database Default Thresholds

To modify database default thresholds for locally managed tablespaces, invoke DBMS_SERVER_ALERT.SET_THRESHOLD as shown in the previous example, but set object_name to NULL. All tablespaces that use the database default are then switched to the new default.

Viewing Alerts

You view alerts by accessing the home page of Enterprise Manager Database Control.

You can also view alerts for locally managed tablespaces with the DBA_OUTSTANDING_ALERTS view. See "Viewing Alert Data" for more information.

<wbr></wbr>

Limitations

Threshold-based alerts have the following limitations:

• Alerts are not issued for locally managed tablespaces that are offline or in read-only mode. However, the database reactivates the alert system for such tablespaces after they become read/write or available.

• When you take a tablespace offline or put it in read-only mode, you should disable the alerts for the tablespace by setting the thresholds to zero. You can then reenable the alerts by resetting the thresholds when the tablespace is once again online and in read/write mode.

See Also:

• "Server-Generated Alerts" for additional information on server-generated alerts in general

• Oracle Database PL/SQL Packages and Types Reference for information on the procedures of the DBMS_SERVER_ALERT package and how to use them

• Oracle Database Performance Tuning Guide for information on using the Automatic Workload Repository to gather statistics on space usage

• "Reclaiming Wasted Space" for various ways to reclaim space that is no longer being used in the tablespace

• "Purging Objects in the Recycle Bin" for information on reclaiming recycle bin space