select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||’MB’ “TOTAL_SIZE”, round((num_rows*avg_row_len
/1024/1024),2)||’Mb’ “ACTUAL_SIZE”, round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||’MB’ “FRAGMENTED_SPACE” from
dba_tables where owner in(‘a’,’b’,’c’,’d’) and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
> 1000 order by 8 desc;
select table_name,round((blocks*8),2) “size (mb)” ,
round((num_rows*avg_row_len/1024),2) “actual_data (mb)”,
(round((blocks*8),2) – round((num_rows*avg_row_len/1024),2)) “wasted_space (mb)”, (round((blocks*8),2) – round((num_rows*avg_row_len/1024),2))/round((blocks*8),2)
from dba_tables
where (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) and owner=’&a’
order by 4 desc;
