|
Tablespace
Monitoring in the Oracle 10g database
|
"When
I despair, I remember that all through history the ways of
truth and love have always won. There have been tyrants, and murderers,
and for a time they can seem invincible, but in the end they always fall.
Think of it - always."
Gandhi
|
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";
|