Oracle Database provides proactive help in managing disk space for tablespaces by alerting you when available space is running low. Two alert thresholds are defined by default: warning and critical. The warning threshold is the limit at which space is beginning to run low. The critical threshold is a serious limit that warrants your immediate attention. The database issues alerts at both thresholds.
There are two ways to specify alert thresholds for both locally managed and dictionary managed tablespaces:
By percent full
For both warning and critical thresholds, when space used becomes greater than or equal to a percent of total space, an alert is issued.
By free space remaining (in kilobytes (KB))
For both warning and critical thresholds, when remaining space falls below an amount in KB, an alert is issued. Free-space-remaining thresholds are more useful for very large tablespaces.
Alerts for locally managed tablespaces are server-generated. For dictionary managed tablespaces, Enterprise Manager provides this functionality. See "Monitoring Database Operations with Server-Generated Alerts" for more information.
New tablespaces are assigned alert thresholds as follows:
Locally managed tablespace—When you create a new locally managed tablespace, it is assigned the default threshold values defined for the database. A newly created database has a default of 85% full for the warning threshold and 97% full for the critical threshold. Defaults for free space remaining thresholds for a new database are both zero (disabled). You can change these database defaults, as described later in this section.
Dictionary managed tablespace—When you create a new dictionary managed tablespace, it is assigned the threshold values that Enterprise Manager lists for "All others" in the metrics categories "Tablespace Free Space (MB) (dictionary managed)" and "Tablespace Space Used (%) (dictionary managed)." You change these values on the Metric and Policy Settings page.
Note:In a database that is upgraded from version 9.x or earlier to 10.x, database defaults for all locally managed tablespace alert thresholds are set to zero. This setting effectively disables the alert mechanism to avoid excessive alerts in a newly migrated database.
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.
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( metrics_id => DBMS_SERVER_ALERT.TABLESPACE_BYT_FREE, warning_operator => DBMS_SERVER_ALERT.OPERATOR_LE, warning_value => '10240', critical_operator => DBMS_SERVER_ALERT.OPERATOR_LE, critical_value => '2048', observation_period => 1, consecutive_occurrences => 1, instance_name => NULL, object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE, object_name => 'USERS'); DBMS_SERVER_ALERT.SET_THRESHOLD( metrics_id => DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL, warning_operator => DBMS_SERVER_ALERT.OPERATOR_GT, warning_value => '0', critical_operator => DBMS_SERVER_ALERT.OPERATOR_GT, critical_value => '0', observation_period => 1, consecutive_occurrences => 1, instance_name => NULL, object_type => DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE, object_name => 'USERS'); END; /
Note:When setting non-zero values for percent-full thresholds, use the greater-than-or-equal-to operator,
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
To modify database default thresholds for locally managed tablespaces, invoke
DBMS_SERVER_ALERT.SET_THRESHOLD as shown in the previous example, but set
NULL. All tablespaces that use the database default are then switched to the new default.
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 "Server-Generated Alerts Data Dictionary Views" for more information.
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.
"Monitoring Database Operations with 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