Automated Space Management in Oracle10g

When a database is migrated, the alert is disabled by default and set to NULL.  The database wide default can be reset as desired.  An ideal setting for warning threshold will result in an alert early enough to ensure enough time to resolve the problem before it becomes critical, and late enough to be not bothered when the space is adequate.

The MMON background process checks for tablespace threshold violations every 10 minutes. An alert is triggered when the threshold has been reached. This alert is cleared when a subsequent MMON finds that the space consumption has gone below the threshold often by appropriate DBA actions.

For temporary tablespaces, the threshold value has to be set up as a limit on the used space in the tablespace. In read-only mode tablespaces or offline tablespaces, alerts should not be flagged as nothing more can be done to those tablespaces.

Undo tablespaces are very similar to temporary tablespaces as other segments can reuse the free space in undo segments. Any extent is reusable if it does not contain any active or unexpired undo. The sum of active and unexpired extents is considered as used space for calculating threshold violations. For tablespaces with autoextensible files, the thresholds are calculated according to the maximum file size specified or to the maximum OS file size.