Query to find fragmentation in oracle tables


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