Tuesday, March 12, 2019

How to perform an online shrink of a segment


This feature has been around since Oracle 11.1, and can be a convenient alternative to reorganizing an object to reclaim wasted space.

First, row movement must be enabled:
alter table SCOTT.EMP enable row movement;

If desirable, use the COMPACT keyword to tell Oracle to defragment the segment space and compact the table rows.
You can then postpone the resetting of the high water mark and the deallocation of the space until off-peak hours:
alter table SCOTT.EMP shrink space compact;

The final phase will do the actual reset the HWM. Note that cursors depending on the object will be invalidated and need to be reparsed:
alter table SCOTT.EMP shrink space;


The official documentation can be found here

Another good source is oracle-base.com

An interesting note regarding shrinking of LOB segments is Doc ID 1451124.1: "How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)?" available at support.oracle.com

No comments:

Post a Comment