Oracle Database 2 Day DBA 11g Release- P6

Oracle Database 2 Day DBA 11g Release- P6:Oracle Database 2 Day DBA is a database administration quick start guide that teachesyou how to perform day-to-day database administrative tasks. The goal of this guideis to help you understand the concepts behind Oracle Database. It teaches you how toperform all common administrative tasks needed to keep the database operational,including how to perform basic troubleshooting and performance monitoringactivities.
Nội dung trích xuất từ tài liệu:
Oracle Database 2 Day DBA 11g Release- P6 Performing Common Database Storage Tasks See Also: ■ About Tablespaces on page 6-6Reclaiming Unused Space This section provides background information about reclaimable unused space in the database, and provides instructions about how to reclaim this space. It contains the following topics: ■ About Reclaimable Unused Space ■ About the Segment Advisor ■ Viewing Segment Advisor Recommendations and Reclaiming Space ■ Running the Segment Advisor Manually About Reclaimable Unused Space Over time, performing insert, update, and delete operations (also referred to as DML operations) on objects within a tablespace can create pockets of empty space that individually are not big enough to be reused. Collectively, these pockets can waste large amounts of space. The sparsely populated objects that result can suffer performance degradation during queries and DML operations. Oracle Database enables you reclaim this empty space with the following two online operations—that is, operations that do not block queries or DML against affected objects while the operations are proceeding: ■ Online segment shrink Online segment shrink operates on table segments and on the segments of the dependent objects of the table, such as indexes and partitions. For each segment, data is compacted to the front of the segment. Free space can either be returned to the tablespace or kept in the segment for future insert operations. Online segment shrink is permitted only on segments in a locally managed tablespace with automatic segment space management. Online segment shrink is an in-place operation; no additional free space in the tablespace is required. ■ Online table redefinition (also known as reorganization) Reorganization relocates the table and its dependent objects in a different part of the tablespace. This operation has the desirable side effect of compacting the data for those objects. For this operation to succeed, however, the tablespace must already have free space equal to the size of the table and its dependent objects. Reorganization is the only permitted operation for dictionary-managed tablespaces or for locally managed tablespaces with manual segment space management. The Segment Advisor generates recommendations for shrinking or reorganizing segments that have a significant amount of reclaimable unused space. See Also: ■ Oracle Database Administrators Guide for more information about online segment shrink and online redefinition of tables About the Segment Advisor The Segment Advisor identifies database objects that have unused space that you can reclaim. It performs its analysis by examining usage and growth statistics and by Managing Database Storage Structures 6-19Performing Common Database Storage Tasks sampling the data in the object. By default, it is configured to run automatically at regular intervals, during all maintenance windows (time periods). You can also run the Segment Advisor manually. The regularly scheduled Segment Advisor task is known as the Automatic Segment Advisor. Results from the advisor are summarized on the Space Summary section of the Database Home page as Segment Advisor Recommendations. See Figure 6–5 on page 6-21. During each run of the Automatic Segment Advisor, only a subset of the segments in the database are analyzed. If you believe that particular segments may have reclaimable unused space, yet those segments do not appear among the Segment Advisor recommendations, the Automatic Segment Advisor may not have chosen them for analysis. You can run the Segment Advisor manually on those segments at any time. See Oracle Database Administrators Guide for information about how the Automatic Segment Advisor selects tablespaces and segments for analysis. Segment Advisor Recommendations Segment Advisor recommendations are grouped by tablespace. Only tablespace names appear on the Segment Advisor ...