Does Tablespace Fragmentation Exist in Oracle?

 Tablespace fragmentation is quite a controversial topic in Oracle database administration as some people get really worried about it whereas some people believe that fragmentation doesn't exist in Oracle the way its architecture is.

As per Tom Kyte, "My definition of fragmentation is that you have many "small" holes (regions of contigous free space) that are too small to be the NEXT extent of any object. These holes of free space resulted from dropping some objects (or truncating them) and the resulting free extents cannot be used by any other object in that tablespace. This is a direct result of using a pctincrease that is not zero and having many wierd sized extents (every extent is a unique size and shape).."

You can use following query to see any fragmentation in the Oracle tablespace is there is any:

set lines 150

set pages 150

col tablespace_name head "Tablespace|Name" for a25

col total_mbytes    head "Total Size|Mb"

col free_mbytes     head "Free Space|Mb"

col free_pct        head "Percent|Free"    for 990.9

col num_ext         head "Total|Extents"   for 9999999

col min_ext   head "Minimum|Size|Kb"        for 9999999

col ext_64k   head "Number|Extents|< 64k"   for 9999999

col ext_128k  head "Number|Extents|< 128k"  for 9999999

col ext_256k  head "Number|Extents|< 256k"  for 9999999

col ext_512k  head "Number|Extents|< 512k"  for 9999999

col ext_1m    head "Number|Extents|< 1m"    for 9999999

col ext_2m    head "Number|Extents|< 2m"    for 9999999

col ext_4m    head "Number|Extents|< 4m"    for 9999999

col ext_8m    head "Number|Extents|< 8m"    for 9999999

col ext_16m   head "Number|Extents|< 16m"   for 9999999

col ext_32m   head "Number|Extents|< 32m"   for 9999999

col ext_64m   head "Number|Extents|< 64m"   for 9999999

col ext_256m  head "Number|Extents|< 256m"  for 9999999

col ext_1g    head "Number|Extents|< 1g"    for 9999999

col ext_8g    head "Number|Extents|< 8g"    for 9999999

col ext_large head "Number|Extents|> 8g"   for 9999999

col max_ext   head "Maximum|Size|Mb"        for 9999999

select fs.file_id

     , count(fs.bytes)                                                             num_ext

     , min(fs.bytes/1024)                                                          min_ext

     , sum(case when fs.bytes/1024 between       0 and       64 then 1 else 0 end) ext_64k

     , sum(case when fs.bytes/1024 between      64 and      256 then 1 else 0 end) ext_256k

     , sum(case when fs.bytes/1024 between     256 and     1024 then 1 else 0 end) ext_1m

     , sum(case when fs.bytes/1024 between    1024 and     4096 then 1 else 0 end) ext_4m

     , sum(case when fs.bytes/1024 between    4096 and    16384 then 1 else 0 end) ext_16m

     , sum(case when fs.bytes/1024 between   16384 and    65536 then 1 else 0 end) ext_64m

     , sum(case when fs.bytes/1024 between   65536 and   262144 then 1 else 0 end) ext_256m

     , sum(case when fs.bytes/1024 between  262144 and  1048576 then 1 else 0 end) ext_1g

     , sum(case when fs.bytes/1024 between 1048576 and  8388608 then 1 else 0 end) ext_8g

     , sum(case when fs.bytes/1024 > 8388608 then 1 else 0 end)                    ext_large

     , round(max(fs.bytes/1024/1024))                                              max_ext

from   dba_free_space  fs

where  fs.tablespace_name = upper('&TSName')

group by fs.file_id

order by fs.file_id