Questions on Tablespace Monitoring

Reference: http://www.iselfschooling.com/mcd12_ora10g/Oracle10g_Answers.htm

Q: How an alert will raise or clear based on a tablespace size?
A: 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.

Q: When do you need to enable row movement on a segment?
A: 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;

Q: On the USERS tablespace, set a warning threshold of 80% and a critical threshold of 95%.
A:
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.

Q: How do you check the database-wide threshold values for the USERS tablespace?
A:
SQL> SELECT warning_value, critical_value
FROM dba_thresholds
WHERE metrics_name = ‘Tablespace Space Usage’ AND
object_name = ‘USERS’
/

Q: How do you turn off the space-usage tracking for the USER tablespace?
A:
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;

Q: How do you reset the database-wide threshold values of the USERS tablespace to the default database values?
A:
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;

Q: How do you check the status of your threshold?
A:
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’;