Do the following to shrink space and re-adjust the high-water mark for a table:
1. Enable row movement for the table.
2. Use the ALTER TABLE…SHRINK SPACE statement to free up unused space.
When you shrink a table, this requires that rows (if any) be moved. This means you must enable row movement. This example enables row movement for the INV table:
SQL> alter table inv enable row movement;
Next the table shrink operation is executed via an ALTER TABLE statement:
SQL> alter table inv shrink space;
You can also shrink the space associated with any index segments via the CASCADE clause:
SQL> alter table inv shrink space cascade;
When you shrink a table, Oracle re-organizes the blocks in a manner that consumes the least amount of space. Oracle also re-adjusts the table’s high-water mark. This has performance implications for queries that result in full table scans. In these scenarios, Oracle will inspect every block below the high-water mark. If you notice that it takes a long time for a query to return results when there aren’t many rows in the table, this may be an indication that there are many unused blocks (because data was deleted) below the high-water mark.
You can instruct Oracle to not re-adjust the high-water mark when shrinking a table. This is done via the COMPACT clause—for example:
SQL> alter table inv shrink space compact;
When you use COMPACT, Oracle defragments the table but doesn’t alter the high-water mark. You will need to use the ALTER TABLE…SHRINK SPACE statement to reset the high-water mark. You might want to do this because you’re concerned about the time it takes to defragment and adjust the high-water mark. This allows you to shrink a table in two shorter steps instead of one longer operation.