Query to find fragmentation in oracle tables
select table_name,round((blocks*8),2)/1024 "size (kb)" ,
round((num_rows*avg_row_len/1024),2)/1024 "actual_data (kb)",
((round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)))/1024 "wasted_space (kb)"
from dba_tables
where
(round((blocks*8),2) > round((num_rows*avg_row_len/1024),2)) and
table_name in ('DMI_QUEUE_ITEM_S','SIGI_EMAIL_IMPORT_STATUS') and owner='ECM'
order by 4 desc
No comments:
Post a Comment