 |
|
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')
 |
If you like Oracle tuning, see the
book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning
tips and scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
