REM Look for the number of chunks which are free,look for also the number of extents being used by objects..primarily for indexes..Also this

set pagesize 300
spool frag.rpt
Prompt Fragmentation Report for the tablespaces
start ./title132.sql "Tablespace Fragmentation report" "frag.sql"
column sumbytes format 999,999,999,9999
column extents forma 999999999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999,999,999
column Tot_Free format 999,999,999,999
column Pct_Free format 999
column Chunks_Free format 9999
column Max_Free format 9999999999
set echo off

PROMPT SPACE AVAILABLE IN TABLESPACES

select a.tablespace_name,sum(a.tots) Tot_Size,
sum(a.sumb) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
(sum(a.largest)/1024)/1024 Max_Free,sum(a.chunks) Chunks_Free
from
(
select tablespace_name,0 tots,sum(bytes) sumb,
max(bytes) largest,count(*) chunks
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0,0,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name;

column owner format a15
column segment_name format a30


PROMPT TABLES WITH MORE THAN 20 EXTENTS

select substr(owner,1,18)Owner,substr(tablespace_name,1,30) tblspc,substr(segment_name,1,30) Name ,segment_type,extents,bytes,max_extents,next_extent
from dba_segments
where segment_type in ('TABLE') and extents>20
and owner not in ('SYS','SYSTEM')
order by owner,segment_name;

PROMPT INDEXES WITH MORE THAN 20 EXTENTS

select substr(owner,1,18)Owner,substr(tablespace_name,1,30) tblspc,substr(segment_name,1,30) Name ,segment_type,extents,bytes ,
max_extents,next_extent
from dba_segments
where segment_type in ('INDEX') and extents>20
and owner not in ('SYS','SYSTEM')
order by owner,segment_name

spool off
/