|
‘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
Introduction
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’s
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-Setting
threshold
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
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’;
|
“Like an unchecked
cancer, hate corrodes the personality and eats away its vital
unity. Hate destroys a man's sense of values and his
objectivity. It causes him to describe the beautiful as ugly and
the ugly as beautiful, and to confuse the true with the false
and the false with the true.” Martin Luther King Jr. |
Questions:
Questions on
Tablespace
Monitoring in the Oracle 10g database
Q: How an alert will raise
or clear based on a tablespace size?
Q: When do you need to
enable row movement on a segment?
Q: On the USERS tablespace,
set a warning threshold of 80% and a critical threshold of 95%.
Q: How do you check the
database-wide threshold values for the USERS tablespace?
Q: How do you turn off the
space-usage tracking for the USER tablespace?
Q: How do you reset the
database-wide threshold values of the USERS tablespace to the default
database values?
Q: How do you check the
status of your threshold?
|