Oracle Shrink Tables
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.
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)