Table defragmentation using Shrinking Solution in Oracle

Table defragmentation using Shrinking Solution in Oracle

Shrinking tables on Fragmentation

Get the Current status

ANALYZE TABLE CUST COMPUTE STATISTICS;

SELECT BLOCKS, BLOCKS*8192/1024 TOTAL_SIZE_KB, AVG_SPACE, 
round(BLOCKS*AVG_SPACE/1024,2) FREE_SPACE_KB
FROM USER_TABLES WHERE TABLE_NAME='&table_name';

Shrink Table

ALTER TABLE table_name ENABLE ROW MOVEMENT;
ALTER TABLE table_name SHRINK SPACE CASCADE;