Try this....

set pages 50

PROMPT
PROMPT Tablespace Freespace Fragmentation Report
PROMPT

column "Blocks" format 999999
column "Free" format 999999
column "Pieces" format 99999
column "Biggest" format 999999
column "Smallest" format 999999
column "Average" format 999999
column "Dead" format 9999
select substr(ts.tablespace_name,1,12) "Tspace",
tf.blocks "Blocks",
sum(f.blocks) "Free",
count(*) "Pieces",
max(f.blocks) "Biggest",
min(f.blocks) "Smallest",
round(avg(f.blocks)) "Average",
sum(decode(sign(f.blocks-5),-1,f.blocks,0)) "Dead"
from dba_free_space f,
dba_data_files tf,
dba_tablespaces ts
where ts.tablespace_name=f.tablespace_name
and ts.tablespace_name = tf.tablespace_name
group by ts.tablespace_name,tf.blocks
/

exit



Ryan Froman

Database Administrator
Support and Development Services
Information Technology Division

Telephone: (08) 9449 XXXX



|---------+--------------------------------------------------------->
| | "cisanchez" |
| | <oracledba-ezmlmshield-x83655131.[Email Address Removed] | azyDBA.com> |
| | |
| | |
| | |
| | 07/10/2004 07:28 AM |
| | |
|---------+--------------------------------------------------------->
>-----------------------------------------------------------------------------------------------|
| |
| To: "LazyDBA Discussion" <[Email address protected] |
| cc: |
| 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



Aviso de confidencialidad:
Este mensaje de correo electrónico contiene información estrictamente
confidencial no susceptible de ser distribuida. Si usted no es el
destinatario de este mensaje, por favor no publicarlo, copiarlo o tomar
cualquier otro tipo de accion sobre esta transmision. Si recibio este
mensaje por error, por favor notifiquenoslo y eliminelo lo antes posible.

Warning of confidentiality:
This message of electronic mail contains strictly confidential information
nonsusceptible to be distributed. If you are not the adressee of this
message, please do not publish it, copy it or take any other kind of action
on this transmission. If you got this message by mistake, please notify it
to us and eliminate it as soon as possible.



--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these terms:
http://www.lazydba.com/legal.html








_______________________________________________________________________________
Unencrypted electronic mail is not secure and may not be authentic.
If you have any doubts as to the contents please telephone to confirm.

This electronic transmission is intended only for those to whom it is
addressed. It may contain information that is confidential, privileged
or exempt from disclosure by law. Any claim to privilege is not waived
or lost by reason of mistaken transmission of this information.
If you are not the intended recipient you must not distribute or copy this
transmission and should please notify the sender. Your costs for doing
this will be reimbursed by the sender.
_______________________________________________________________________________


Oracle LazyDBA home page