iSelfSchooling - 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’;