Here is a query that I found on the internet for determining tablespace
fragmentation. It might help:

select tablespace_name
, sqrt(max(blocks)/sum(blocks)) *
(100/sqrt(sqrt(count(blocks)))) fsfi
from dba_free_space
group by tablespace_name
order by 1

Pamela Cameron

-----Original Message-----
From: cisanchez
[mailto:oracledba-ezmlmshield-x83655131.[Email address protected]
Sent: Wednesday, October 06, 2004 6:29 PM
To: LazyDBA Discussion
Subject: Fragmentation





Hi guys,
Do you any script to run in my database so i can evaluate if a tablespace, a
table or an individual index are fragmented? which considerations do i have
to take care to considere each of this objects
(tablespace,table,index) fragmented? My database is 8.1.7.3 and all of my
tablespaces are Diccionary managed (i know i must change them to locally
managed, but its not planned so closely. My database is an R3 SAP on UNIX)


Saludos
Christian I. Sánchez Noriega




Oracle LazyDBA home page