Segment Shrinking

Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark, it is called segment shrinking and is only possible for segments which use Automatic Segment Space Management and locally managed.

Steps to perform segment shrink.

Step 1: Enable Row movement for the segment

SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

Step 2: Recover space and amend the high water mark (HWM).

SQL> ALTER TABLE scott.emp SHRINK SPACE;

Step 2a: Recover space, but don't amend the high water mark (HWM).

SQL> ALTER TABLE scott.emp SHRINK SPACE COMPACT;

Step 3: Recover space for the object and all dependant objects.

SQL> ALTER TABLE scott.emp SHRINK SPACE CASCADE;

Note: 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 dependant SQL statements will need to be reparsed.

Thanks