Oracle Shrink Tables

Jump Ahead

Over time, updates and deletes on objects within a tablespace can create pockets of empty space that individually are not large enough to be reused for new data. This type of empty space is referred to as fragmented free space.

Shrink Table

Command to check the size of block:

SELECT BLOCKS FROM DBA_TABLES WHERE TABLE_NAME=’EMP’;

Make sure you compute statistics before checking the block size:

ANALYZE TABLE EMP COMPUTE STATISTICS;

Based on recommendations, follow below commands

ALTER TABLE SCOTT.EMP ENABLE ROW MOVEMENT;

ALTER TABLE SCOTT.EMP SHRINK SPACE COMPACT;

OR

ALTER TABLE SCOTT.EMP SHRINK SPACE CASCADE; 

(To recover space for the objects and all dependent objects)

Was this article helpful?

Related Articles

Leave a Comment