Reclaiming Unused Space - Oracle 10g

Over time, it is common for segment space to become fragmented or for a segment to acquire a lot of free space as the result of update and delete operations. The resulting sparsely populated objects can suffer performance degradation during queries and DML operations.

The Segment Advisor can help you determine which objects have space available for reclamation. If the Segment Advisor indicates that an object does have space available for reclamation, you have two ways to release the space so that it can be used by other segments: you can compact and shrink database segments or you can deallocate unused space at the end of a database segment. The Segment Advisor relies for its analysis on data collected in the Automatic Workload Repository (AWR). An adjustment to the collection interval and retention period for AWR statistics can affect the precision and the type of recommendations the advisor produces.

Segment Advisor

Oracle Database provides a Segment Advisor that helps you determine whether an object has space available for reclamation based on the level of space fragmentation within the object. The Segment Advisor can generate advice at three levels:

  • At the object level, advice is generated for the entire object, such as a table. If the object is partitioned, then the advice is generated on all the partitions of the object. However, advice does not cascade to dependent objects such as indexes, LOB segments, and so forth.
  • At the segment level, the advice is generated for a single segment, such as unpartitioned table, a partition or subpartition of a partitioned table, or an index or LOB column.
  • At the tablespace level, advice is generated for every segment in the tablespace.

The best way to invoke the Segment Advisor is through Enterprise Manager. Please refer to Oracle 2 Day DBA for more information on how to use Enterprise Manager to invoke the Segment Advisor. In addition, you activate the Segment Advisor using procedures of the DBMS_ADVISOR package. Please refer to PL/SQL Packages and Types Reference for details on these parameters. The following procedures are relevant for the Segment Advisor:

CREATE_TASK Use this procedure to create the Segment Advisor Task. Specify 'Segment Advisor' as the value of the ADVISOR_NAME parameter.

CREATE_OBJECT Use this procedure to identify the target object for segment space advice. The parameter values of this procedure depend upon the object type.

Input for DBMS _ADVISOR.CREATE _OBJECT

Input for DBMS _ADVISOR.CREATE _OBJECTInput for DBMS _ADVISOR.CREATE _OBJECT

SET_TASK_PARAMETER Use this procedure to describe the segment advice you need.

Input for DBMS _ADVISOR.SET _TASK _PARAMETER

Input for DBMS _ADVISOR.SET _TASK _PARAMETER

The example that follows shows how to use the DBMS_ADVISOR procedures to activate the Segment Advisor for the sample table hr.employees. The user executing these procedures must have the EXECUTE object privilege on the package or the ADVISOR system privilege:


The Segment Advisor creates several types of results:

Findings If you have specified TRUE for RECOMMEND_ALL in the SET_TASK_PARAMETER procedure, then the advisor generates a finding for each segment that qualifies for analysis. You can retrieve the findings by querying the DBA_ADVISOR_ FINDINGS data dictionary view.

Recommendations If segment shrink would result in benefit, then the advisor generates a recommendation for the segment. You can retrieve the recommendations by querying the DBA_ADVISOR_RECOMMENDATIONS dictionary view.

Actions Every advisory recommendation is associated with a suggested action to perform segment shrink. You can retrieve the action by querying the DBA_ADVISOR_ACTIONS data dictionary review. This view provides the SQL you need to perform segment shrink.

Objects All findings, recommendations, and actions are associated with an object. If the input to the advisor results in analysis of more than one segment, as with a tablespace or partitioned table, then one entry is created for each segment in the DBA _ADVISOR _OBJECTS dictionary view. You can correlate the object in this view with the object in the findings, recommendations, and actions views. Please refer to Oracle Database Reference for a description of these views.

Database Reference for a description

Shrinking Database Segments

Oracle Database lets you shrink space in a table, index-organized table, index, partition, subpartition, materialized view, or materialized view log. You do this using ALTER TABLE, ALTER INDEX, ALTER MATERIALIZED VIEW, or ALTER MATERIALIZED VIEW LOG statement with the SHRINK SPACE clause. Shrink operations can be performed only on segments in tablespaces with automatic segment-space management. As with other DDL operations, segment shrink causes subsequent SQL statements to be reparsed because of invalidation of cursors unless you specify the COMPACT clause, described below. Segment shrink is available through the Enterprise Manager interface, which guides you through the required steps. The remainder of this section discusses manually implementing segment shrink.

Segment shrink reclaims unused space both above and below the high water mark. In contrast, space deallocation reclaims unused space only above the high water mark. In shrink operations, by default, the database compacts the segment, adjusts the high water mark, and releases the reclaimed space. Two optional clauses let you control how the shrink operation proceeds:

  • The COMPACT clause lets you divide the shrink segment operation into two phases. When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the reallocation of the space until a future time. This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed. The defragmentation and compaction results are saved to disk, so the data movement does not have to be redone during the second phase. You can reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase.
  • The CASCADE clause extends the segment shrink operation to all dependent segments of the object. For example, if you specify CASCADE when shrinking a table segment, all indexes of the table will also be shrunk. (You need not specify CASCADE to shrink the partitions of a partitioned table.) To see a list of dependent segments of a given object, you can run the OBJECT_DEPENDENT_ SEGMENTS procedure of the DBMS_SPACE package.

Segment shrink is an online, in-place operation. Unlike other space reorganization methods, segment shrink does not require extra disk space to be allocated. Indexes are maintained during the shrink operation and remain usable after the operation is complete. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operation are blocked for a short time at the end of the shrink operation, when the space is deallocated.

Additional benefits of shrink operations are these:

  • Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing (OLTP) performance.
  • The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system (DSS) performance.

Segment shrink requires that rows be moved to new locations. Therefore, you must first enable row movement in the object you want to shrink and disable any rowidbased triggers defined on the object. Segment shrink is not supported for LOB segments or for tables with function-based indexes. Please refer to Oracle Database SQL Reference for the syntax and additional information on shrinking a database object, including restrictions.

Deallocating Unused Space

When you deallocate unused space, the database frees the unused space at the unused (high water mark) end of the database segment and makes the space available for other segments in the tablespace.

Prior to deallocation, you can run the UNUSED_SPACE procedure of the DBMS_SPACE package, which returns information about the position of the high water mark and the amount of unused space in a segment. For segments in locally managed tablespaces with automatic segment-space management, use the SPACE_ USAGE procedure for more accurate information on unused space.

The following statements deallocate unused space in a segment (table, index or cluster):


The KEEP clause is optional and lets you specify the amount of space retained in the segment. You can verify the deallocated space is freed by examining the DBA_FREE_SPACE view.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 10g Topics