Managing Database Resources - OCP: Oracle Database 12c Administrator Certified Professional - page 486

Shrinking Segments

As with the files on the hard drive in your personal computer, the data within Oracle data-
base segments can become fragmented with use. Data Manipulation Language (DML)
operations—namely delete, update, and insert—can cause fragmentation of data and free
space. Fragmentation of free space leads to wasted free space as well as performance issues
such as the following:
Cache Utilization Sparsely populated (fragmented) data blocks in memory require more
reads to get the same amount of data as densely populated (defragmented) data blocks.
Table Scans A full segment scan of fragmented data blocks requires more physical reads
than a scan of defragmented blocks, so full table scans must read more fragmented blocks
than defragmented blocks to get the same results.
There are two methods to defragment a segment online; use either table redefinition,
also referred to as reorganization , or segment shrink. Table redefinition copies a table to
a new location and consolidates the data. This operation requires space for the new copy
of the table and its dependent objects. Also worth mentioning is the method to deallocate
unused space above the high-water mark by issuing the DEALLOCATE UNUSED command.
See Table 10.3 for a comparison of these methods.

TABle 10.3 Comparing Space-Reclamation Methods

Method Reclamation Method

Segment shrink Reclaims space above and below the high-water mark without

using additional space

Reorganization Moves rows to a new physical location, resetting the high-water

mark but using additional space during the operation

Deallocate unused Deallocates space above the high-water mark that is currently

not in use

For segments in dictionary-managed tablespaces or for locally managed
tablespaces with manual segment space management, segment reorganiza-
tion is the only permitted operation for reclaiming fragmented free space.

Online segment shrink compacts the segment in place and does not require additional
space to perform the operation. Segment shrink can be performed on the dependent objects
like indexes and partitions. Segment shrink works on the following objects:

Heap tables

■

Index-organized tables and their overflow segments

■