Oracle Space and Storage Management Enhancements

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

Proactive Tablespace Management

� In Oracle Database 10g, by default, all tablespaces have built-in alerts that   notify you when the free space in the tablespace goes below a certain   predetermined threshold level.

� By default, Oracle sends out a warning alert when your tablespace is 85   percent full and a critical alert when the tablespace is 97 percent full. This   also applies in the undo tablespace.

� If you are migrating to Oracle Database 10g, Oracle turns off the   automatic tablespace alerting mechanism by default.

Tablespace Alerts Limitations

� You can set alerts only for locally managed tablespaces.

� When you take a tablespace offline or make it read-only, you must turn the alerting mechanism off.

� You will get a maximum of only one undo alert during any 24-hour period.

Using the Database Control to Manage Thresholds

Manage Metrics link | click the Edit Thresholds button

Using the DBMS_SERVER_ALERT Package

You can use the procedures: SET_THRESHOLD and GET_THRESHOLD in the DBMS_SERVER_ALERT package to manage database thresholds.

Examples:

To set your own databasewide default threshold values for the Tablespace Space Usage metric:

EXECUTE 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=>95,
OBSERVATION_PERIOD=>1,
CONSECUTIVE_OCCURRENCES=>1,
INSTANCE_NAME=>NULL,
OBJECT_TYPE=>dbms_server_alert.object_type_tablespace,
OBJECT_NAME=>NULL)

To set a warning threshold of 80% and a critical threshold of 95% on the EXAMPLE tablespace, use the same previous example except OBJECT_NAME parameter should take value of

'EXAMPLE'

To turn off the space-usage tracking mechanism for the EXAMPLE tablespace:

EXECUTE DBMS_SERVER_ALERT.SET_THRESHOLD(
METRICS_ID=>dbms_server_alert.tablespace_pct_full,
WARNING_OPERATOR=>dbms_server_alert.operator_do_not_check,
WARNING_VALUE=>'0',
CRITICAL_OPERATOR=>dbms_server_alert.operator_do_not_check,
CRITICAL_VALUE=>'0',
OBSERVATION_PERIOD=>1,
CONSECUTIVE_OCCURRENCES=>1,
INSTANCE_NAME=>NULL,
OBJECT_TYPE=>dbms_server_alert.object_type_tablespace,
OBJECT_NAME=>'EXAMPLE')