|
Tablespace Monitoring in the Oracle 10g database
In the Oracle Database 10g, tablespace thresholds
are defined in terms of a percentage of the
tablespace size. When the threshold crosses their
limits, an appropriate alert will raise or clear.
Since a shrink operation may cause ROWIDs to change
in heap-organized segment, before executing a shrink
operation you should enable row movement on a
segment.
For example:
SQL> ALTER TABLE emp ENABLE ROW MOVEMENT;
SQL> ALTER TABLE emp SHRINK SPACE CASCADE;
Hands-On #1:
On the USERS tablespace, set a warning threshold of
80% and a critical threshold of 95%.
SQL> BEGIN
DBMS_SERVER_ALERT.set_threshold (
DBMS_SERVER_ALERT.tablespace_pct_full,
DBMS_SERVER_ALERT.operator_ge, 80,
DBMS_SERVER_ALERT.operator_ge, 95, 1, 1,
NULL,
DBMS_SERVER_ALERT.object_type_tablespace,
�USERS�);
END;
You can use the NULL value to return to the
database-wide default values.
Check the database-wide threshold values for the
USERS tablespace.
SQL> SELECT warning_value, critical_value
FROM dba_thresholds
WHERE metrics_name = �Tablespace Space
Usage� AND
object_name = �USERS�
/
Hands-On #2:
Turn off the space-usage tracking for the USER
tablespace.
SQL> BEGIN
DBMS_SERVER_ALERT.set_threshold (
DBMS_SERVER_ALERT.tablespace_pct_full,
DBMS_SERVER_ALERT.operator_do_not_check,
�0�,
DBMS_SERVER_ALERT.operator_do_not_check,
�0�, 1, 1, NULL,
DBMS_SERVER_ALERT.object_type_tablespace,
�USERS�);
END;
Reset the database-wide threshold values of the
USERS tablespace to the default database values.
SQL> BEGIN
DBMS_SERVER_ALERT.set_threshold (
DBMS_SERVER_ALERT.tablespace_pct_full,
NULL, NULL, NULL, NULL, 1, 1, NULL,
DBMS_SERVER_ALERT.object_type_tablespace, �USERS�);
END;
You can check the status of your threshold by using
the following SQL statements.
SQL> SELECT reason, resolution
FROM dba_alert_history
WHERE object_name = �USERS�;
SQL> SELECT reason, message_level
FROM dba_outstanding_alerts
WHERE object_name = �USERS�;
|
