iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 10g New Features

 

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

 

 

Google
 
Web web site