Wednesday, November 13, 2013

script to list fragmented tables on oracle database schema?

select owner,table_name,round((blocks*8192),2)/1024/1024 "size (MB)" , round((num_rows*avg_row_len/1024/1024),2) "actual_data (MB)",
round((round((blocks*8192),2)/1024/1024 - round((num_rows*avg_row_len/1024/1024),2)),1) "wasted_space (MB)"
from dba_tables where (round((blocks*8192),2)/1024/1024 > round((num_rows*avg_row_len/1024/1024),2))
and (round((round((blocks*8192),2)/1024/1024 - round((num_rows*avg_row_len/1024/1024),2)),1)) > 100 and owner='ABC'
order by 5 desc;

No comments:

Post a Comment