Steps:
SQL>
exec dbms_stats.gather_table_stats('HR','EMPLOYEES');
-- Gathering table statistics
SQL>
SELECT owner,
table_name,
ROUND ( (num_rows * avg_row_len) / (1024 * 1024)) MB
FROM dba_tables
WHERE table_name = 'EMPLOYEES';
-- Check table actual size
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Check table current, actual, wasted size
SQL>
ALTER TABLE HR.EMPLOYEES ENABLE ROW MOVEMENT;
-- Enable feature before the operation
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE COMPACT;
-- Online operation without HWM (High Water Mark) fix
SQL>
ALTER TABLE HR.EMPLOYEES SHRINK SPACE;
-- Table will be locked for any DML commands
SQL>
ALTER TABLE HR.EMPLOYEES DISABLE ROW MOVEMENT;
-- Disable feature after the operation
SQL>
SELECT table_name,
ROUND ( (blocks * 8) / 1024, 2) "size (MB)",
ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2) "actual_data (MB)",
( ROUND ( (blocks * 8) / 1024, 2)
- ROUND ( (num_rows * avg_row_len / 1024 / 1024), 2))
"wasted_space (MB)"
FROM dba_tables
WHERE (ROUND ( (blocks * 8), 2) >
ROUND ( (num_rows * avg_row_len / 1024), 2))
AND table_name = 'EMPLOYEES'
AND OWNER LIKE 'HR'
ORDER BY 4 DESC;
-- Lastly, Check table current, actual, wasted size again
Ref: http://select-star-from.blogspot.com.tr/2013/09/how-to-check-table-fragmentation-in.html
