How to check tables fragmentation in Oracle DB?

#oracle #database #performance #optimization

To check tables fragmentation in Oracle DB run the following sql command:

select 
    table_name,
    avg_row_len,
    round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
    round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
    round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
    (round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/round(((blocks*16/1024)),2))*100 "percentage"
from 
    all_tables 
where
  round(( (blocks*16/1024) ),2) > 0
order by 6 desc;