Monday, September 26, 2011

Finding Tablespace Fragmentation


Below is the script to find out the level of tablespace fragmentation.


select
 tablespace_name,
 count(*) free_chunks,
 decode (round((max(bytes) / 1024000), 2), null, 0,
 round((max(bytes) / 1024000), 2)) largest_chunk,
  nvl (round(sqrt(max(blocks)/sum(blocks))*
 (100/sqrt(sqrt(count(blocks)) )), 2), 0)
 fragmentation_index
from
 sys.dba_free_space
group by
 tablespace_name
order by
 2 desc, 1

1 comment:

  1. How to ressolve TBS fragmentation on a EE database?

    ReplyDelete