# 
#   Name:   extents.sql
# 
#   Author: Mark Gurry
# 
#   Description:  This is a simple script that must be run by a DBA.
#                 The script reports on all segments having more than 3 
#                 extents which may be candidates for re-building. The
#                 second  script shows me the total Free Space
#                 for each Tablespace and the Third script provides me with
#                 the largest extent pieces > 250000 bytes in each tablespace.
# 
#   Input:  Nil
# 
#   Output: Spooled file called extents.lis 
#
column segment_name format a22;
column owner      format a12;
column tablespace_name format a16;
column no_extents format 999;
column seg_size   format 999,999,999;
set pagesize 999
spool extents.lis
break on owner skip 1;
ttitle '                    Database Fragmentation Report ';
select owner,segment_name,tablespace_name,
         count(*) no_extents , sum(bytes) seg_size
     from sys.dba_extents
group by owner,segment_name,tablespace_name
having count(*) > 3;
break on tablespace_name skip 1;
ttitle '                    Database Freespace Report';
select tablespace_name, sum(bytes) freebytes
from   user_free_space
group by tablespace_name;
ttitle '                    Database Free Extent Size Report';
select tablespace_name, Bytes freebytes
from   user_free_space
where  bytes > 250000
order by Tablespace_name, Bytes desc     ;
spool off