13.6. Summary

This chapter presented an in-depth tour of the automatic features that can help you manage tablespaces and the segments in the tablespaces in a proactive, instead of a reactive, manner.

Tablespaces can be monitored proactively when they reach one of two thresholds, and an alert can be generated to notify you that a tablespace has crossed the warning or critical threshold level. We showed how the PL/SQL package DBMS_SERVER_ALERT gives you a way to pro-grammatically change these thresholds.

The Segment Advisor and segment shrink work together to not only find segments that have unused space but also to compact the space in a segment and free up the space for other database objects. We also showed you how other advisors such as the Undo Advisor and the Redo Logfile Size Advisor can help you manage and optimize disk space and performance for the undo tablespace and the redo log files, respectively.

We described a number of alternatives to the standard heap-based tables: index-organized tables and clustered tables. Index-organized tables provide a distinct performance and space usage benefit for those tables frequently accessed by the primary key because the row data is stored within the index itself. Clustered tables provide a distinct benefit for tables that are frequently queried together by storing those tables within the same segment. A variation on clustered tables— sorted hash clusters—provides you not only with another way to use disk space efficiently but also to optimize the performance of applications that use data in a first-in, first-out (FIFO) fashion.

Finally, we explained an Oracle feature that prevents rework when out-of-space conditions occur: Resumable Space Allocation. For those sessions with Resumable Space Allocation enabled, the long-running operation that runs out of disk space is temporarily suspended until the DBA provides additional disk space, allowing the long-running operation to complete successfully.