Chapter 13. Monitoring and Managing Storage

ORACLE DATABASE 10G: ADMINISTRATION II EXAM OBJECTIVES COVERED IN THIS CHAPTER:

• Monitoring and Managing Storage

  • Tune redo writing and archiving operations.

  • Issue statements that can be suspended upon encountering space condition errors.

  • Reduce space-related error conditions by proactively managing tablespace usage.

  • Reclaim wasted space from tables and indexes using the segment shrink functionality.

  • Estimate the size of new tables and indexes.

  • Use different storage options to improve the performance of queries.

  • Rebuild indexes online.

Oracle Database 10g (Oracle 10g) provides a number of automated enhancements to help you manage the disk space in the database, both at the tablespace level and at the segment level.

Proactive tablespace monitoring uses the DBMS_SERVER_ALERT PL/SQL package to set up thresholds at which you are notified of a potential space issue; ideally, this happens long before a user calls you because they cannot create a table because of lack of space in a tablespace.

To make table access more space efficient and reduce the amount of I/O needed to access a table, Oracle provides segment shrink functionality to compress a table whose data blocks are sparsely populated. The Segment Advisor notifies you of segments—either table or index seg-ments—that would benefit from a shrink operation.

Other automated advisors introduced in Oracle 10g include the Redo Logfile Size Advisor. The Redo Logfile Size Advisor helps you optimize the size of your redo log files so they are not too big or too small; redo log files that are too small can have an impact on system performance.

As of Oracle 9i, you can prevent the failure of long-running operations that run out of disk space by leveraging Resumable Space Allocation. Instead of rolling back the entire long-running operation, the operation is paused until the DBA can allocate another disk volume or increase a user's quota. Once the disk space is available, the long-running operation resumes where it left off.

In all of these cases, the Oracle Enterprise Manager (EM) Database Control provides wizards and a graphical interface for these enhancements, making it easy to leverage these enhancements when the command-line syntax is unfamiliar or difficult to remember.

In this chapter, we will review how to set up server alerts, both with the PL/SQL interface and with EM Database Control. We will also explain how to identify segments that can benefit from space reclamation using the Segment Advisor and how to shrink these segments with segment shrink operations. We will present a few other table types that can optimize space usage, performance, or both; these table types include index-organized tables and clustered tables, including hash clusters and sorted hash clusters. Finally, we will provide an in-depth look at Resumable Space Allocation and how it can save both time and wasted effort when a long-running operation runs out of disk space.