Oracle has alter table shrink space command to reclaim unused disk space for objects (tables and indexes). This command available from version 10g ease the DBA job from doing table export and import which needs downtime.
It’s recommended to run the alter table shrink space during off-peak hour with less database activity. DBA needs to know what data segments experience HWM (high watermark) in order to reclaim free space to the database and shrink segments.
A shrink could fail because the tablespace is not locally managed or do not have ASSM (automatic segment managed) enabled, the table has a column of long, row movement has not been enabled or it is a clustered table.
- Segment shrink is allowed only on segments whose space is automatically managed
- Heap-organised, index-organised and cluster tables can be shrunk
- ROW MOVEMENT must be enabled for heap-organised tables
- Chained rows may be repaired during a segment shrink operation
- Triggers are not fired during a segment shrink operation
- Tables with ROWID-based materialized view cannot be maintained
- IOT mapping tables and overflow segment cannot be shrunk
- Tables with function-based indexes cannot be shrunk
Alter Table Shrink Space Syntax
Perform the alter table shrink space command on the table that has been housekeep by the support team.
SQL> ALTER TABLE schema.table_name enable ROW movement;
SQL> ALTER TABLE schema.table_name shrink SPACE;
SQL> ALTER TABLE schema.table_name disable ROW movement;
It’s not guaranteed that all chained rows will be fixed because not all blocks may be read in a segment shrink operation.
However there’s benefits of a shrink operation which are:
- Full table scans will take less time (a table scan will always scan up to the HWM even if space is not used)
- Better index access takes place because of a smaller B-Tree
- Space is freed up for other database objects
- Space below the HWM is released and the HWM is moved down
Next» Gather Schema Statistics In Oracle