Oracle Shrink Tablespace Usage

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.

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.