Table Fragmentation

To get Actual, Optimal, Reclaimable Size from the tables of a specific user.

set linesize 200 trimspool on
col owner for a22
col table_name for a30
col tablespace_name for a20
select t.owner,t.table_name, s.tablespace_name,
round(s.bytes/1024/1024) actual_MB,
round((t.num_rows+1) * t.avg_row_len/1024/1024) optimal_MB,
round(s.bytes/1024/1024) -
round((t.num_rows+1) * t.avg_row_len/1024/1024) CLAIMABLE_MB
from dba_tables t, dba_segments s
where t.owner = s.owner and s.owner='&owner_name'
and  t.table_name = s.segment_name
and round(s.bytes/1024/1024) - round((t.num_rows+1) * t.avg_row_len/1024/1024) > 200
order by CLAIMABLE_MB desc
/

Sample Output:-

From above screenshots, By perform Table reorganization 19gb, 11gb, 3gb …… Space can be reclaim from the tables.