How to shrink segments

Online Segment Shrink for Tables, LOBs and IOTs using ALTER TABLE … SHRINK SPACE Command.

The ALTER TABLE … SHRINK SPACE command was introduced in Oracle 10g to perform online segment shrinks for tables, LOBs and IOT overflow segments.

Shrink Space Examples
Here are some simple examples 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 COMPACT option allows the shrink operation to be broken into two stages. First the rows are moved using the COMPACT option but the high water mark (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 dependent SQL statements will need to be re-parsed.

Other shrink commands of interest are displayed below.

-- Shrink a LOB segment (basicfile only until 21c).
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);

-- Shrink an IOT overflow segment.
ALTER TABLE iot_name OVERFLOW SHRINK SPACE;

There is more detail about this functionality below.

Comments and Restrictions

Here are some things to consider before performing shrink operations.

Moving rows can cause problem with rowid based triggers.
Rowid materialized views must be rebuilt after a shrink operation.
The shrinking process is only available for objects in tablespaces with automatic segment-space management enabled.
You can’t combine the SHRINK SPACE clause with any other ALTER TABLE clauses.
You can’t shrink a cluster or a clustered table.
You can’t shrink any object with a LONG column.
You can’t shrink tables with dependent function-based indexes, domain indexes, or bitmap join indexes.
You can’t shrink tables that are the master table of an ON COMMIT materialized view
Mapping tables of index-organized tables are not affected by a shrink.
Shrinks can’t be used for compressed tables, except those using Advanced Row Compression (ROW STORE COMPRESS ADVANCED).
The shrink operation against a table doesn’t cascade to the LOB segments. They need to handled separately.
You can’t shrink securefile LOB segments.
Changing the arrangement of rows in a table can have a negative impact on performance in some circumstances. Test thoroughly before making any decisions.
After any structural change, like a move, remember to check for unusuable indexes. You can use the sunableidx.sql script to find them. If you have any, rebuild them.