DBA Script: Look for number of extents

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

Code:
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