Online Segment Shrink

Why row movement to be enabled before shrinking the segments?

The shrinking is accomplished by moving rows between blocks,hence the requirement for row movement to be enabled for the shrink to take place. This can cause problem with ROWID based triggers. The shrinking process is only available for objects in tablespaces with automatic segment-space management enabled.


Online Segment Shrink
==================
Based on the recommendations from the segment advisor you can recover space from specific objects using one of the variations of the ALTER TABLE ... SHRINK SPACE command.

-- Enable row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

-- Recover space and amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

-- Recover space, but don't amend the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover space for the object and all dependant objects.
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

The shrink is accomplished by moving rows between blocks, hence the requirement for row movement to be enabled for the shrink to take place. This can cause problem with ROWID based triggers. The shrinking process is only available for objects in tablespaces with automatic segment-space management enabled.

The COMPACT option allows the shrink operation to be broken into two stages. First the rows are moved using the COMPACT option but the HWM is not adjusted so no parsed SQL statements are invalidated. The HWM can be adjusted at a later date by reissuing the statement without the COMPACT option. At this point any depenedant SQL statements will need to be reparsed.

Other shrink commands of interest are displayed below.

-- Shrink a LOB segment.
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);

-- Shrink an IOT overflow segment.

ALTER TABLE iot_name OVERFLOW SHRINK SPACE;