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 degrade performance during queries and DML operations.

Oracle Database enables you reclaim this empty space with the following 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 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.