Find Waste SPACE in a TABLE

Find Waste SPACE in a TABLE


select 
a.owner, 
a.segment_name, 
a.segment_type, 
round(a.bytes/1024/1024,0) MBS, 
round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) WASTED 
from dba_segments a, dba_tables b 
where a.owner=b.owner 
and a.owner ='USERNAME'
and a.segment_name = b.table_name 
and a.segment_type='TABLE' 
group by a.owner, a.segment_name, a.segment_type, round(a.bytes/1024/1024,0) ,round((a.bytes-(b.num_rows*b.avg_row_len) )/1024/1024,0) 
HAVING ROUND(BYTES/1024/1024,0) >100 
order by round(bytes/1024/1024,0) desc;