Oracle Segment Shrink

oracle_segment_shrink.sql

-- ROW MOVEMENT is required to move rows.
-- SHRINK SPACE COMPACT moves the rows without resetting the high water mark.
-- SHRINK SPACE moves the rows and resets the high water mark which temporarily locks the table.
-- May need to rebuild the indexes after a shrink.

alter table [tableName] enable row movement;
alter table [tableName] shrink space compact;
alter table [tableName] shrink space;

alter index [indexName] shrink space compact;
alter index [indexName] shrink space;