Fragmentation in Oracle

A nice link on fragementation by Jonathan:

http://jonathanlewis.wordpress.com/2010/07/13/fragmentation-1/


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)
> 100 order by 8 desc;

select owner,table_name,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"
    ,((round((blocks*8),2)-round((num_rows*avg_row_len/1024),2))/round((blocks*8),2))*100 -10 "reclaimable space % " from
    dba_tables where owner ='ARADMIN' and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)> 100 order by 6 desc;

OWNER      TABLE_NAME                     TOTAL_SIZE           ACTUAL_SIZ FRAGMENTED reclaimable space %
---------- ------------------------------ -------------------- ---------- ---------- --------------------
ARADMIN    T8                             302.01MB             0Mb        302MB                89.9985837
ARADMIN    T2736                          213.34MB             1.04Mb     212.3MB               89.513096
ARADMIN    T979                           1086.19MB            25.78Mb    1060.41MB            87.6265077
ARADMIN    T1929                          10161.25MB           679.22Mb   9482.03MB            83.3156254
ARADMIN    T2841                          7076.23MB            595.62Mb   6480.6MB             81.5827689
ARADMIN    T361                           780.18MB             71.16Mb    709.02MB             80.8790631
ARADMIN    T980                           6597.76MB            673.5Mb    5924.26MB            79.7920369
ARADMIN    T2759                          9262.11MB            1055.49Mb  8206.62MB            78.6041931
ARADMIN    T2154                          450.47MB             57.9Mb     392.57MB             77.1471774
ARADMIN    T1210                          767.18MB             112.05Mb   655.13MB             75.3947176
ARADMIN    T2024                          264.14MB             39.79Mb    224.35MB             74.9369639
ARADMIN    T418                           1559.8MB             316.29Mb   1243.51MB            69.7222971
ARADMIN    T359                           604.87MB             143.63Mb   461.23MB             66.2538813
ARADMIN    T404                           310.02MB             75.5Mb     234.52MB             65.6469489
ARADMIN    T1928                          1852.4MB             457.78Mb   1394.62MB            65.2873291
ARADMIN    T2019                          690.61MB             209.71Mb   480.9MB              59.6342833
ARADMIN    T2504                          470.34MB             147.46Mb   322.88MB             58.6484951
ARADMIN    T1806                          168.52MB             55.23Mb    113.3MB              57.2287968
ARADMIN    T1072                          158.62MB             53.37Mb    105.24MB             56.3502438
ARADMIN    T2317                          2073.16MB            733.76Mb   1339.41MB            54.6068853
ARADMIN    T1934                          2548.71MB            917.07Mb   1631.64MB            54.0181464
ARADMIN    T2522                          841.88MB             310.7Mb    531.18MB             53.0947827
ARADMIN    T1465                          2718.54MB            1054.73Mb  1663.81MB            51.2022025
ARADMIN    T2393                          183.47MB             71.37Mb    112.1MB               51.099456
ARADMIN    T2528                          676.53MB             269.43Mb   407.1MB               50.175239
ARADMIN    T1474                          729.33MB             301.83Mb   427.5MB              48.6158266
ARADMIN    T2840                          1241.96MB            668.17Mb   573.79MB             36.2004737
ARADMIN    T1906                          850.85MB             490.98Mb   359.88MB             32.2960201
ARADMIN    T2180                          1339.9MB             836.15Mb   503.75MB             27.5963496
ARADMIN    T2842                          1210.99MB            780.78Mb   430.21MB             25.5256166
ARADMIN    T2144                          1087.13MB            720.73Mb   366.4MB              23.7034218
ARADMIN    T2278                          361.77MB             241.08Mb   120.69MB             23.3611924
ARADMIN    T2839                          463.37MB             353.99Mb   109.38MB             13.6056697
ARADMIN    T1445                          717.38MB             575.82Mb   141.56MB             9.73296061