REM PURPOSE:
REM    This script displays information about space in tablespaces,
REM fragmentation and potential extent-problems. It has three sections:
REM
REM 1. Space available per tablespace.
REM       Shows total size in bytes, total free space in bytes,
REM       percentage free space to total space,
REM  the size of the largest contigous free segment and
REM
REM    2. Lists tables and indexes with more than 20 extents.
REM
REM    3. Lists tables/indexes whose next extent will not fit
REM
REM
------------------------------------------------------------------------

set linesize 132
set pagesize 300

column sumb format 999,999,999,999
column extents forma 9999
column bytes format 999,999,999
column largest format 999,999,999
column Tot_Size format 999,999,999,999
column Tot_Free format 999,999,999,999
column Pct_Free format 999,999,999
column Chunks_Free format 999,999,999
column Max_Free format 999,999,999
set echo off
set termout=off
spool space_info.rpt

PROMPT  SPACE AVAILABLE IN TABLESPACES
REM select 'SPACE AVAILABLE IN &1' FROM DUAL;

select substr(a.tablespace_name,1,10),sum(a.tots) Tot_Size,
sum(a.sumb) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
sum(a.largest) 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   SEGMENTS WITH MORE THAN 20 EXTENTS

select owner,segment_name,extents,bytes ,
max_extents,next_extent
from  dba_segments
where segment_type in ('TABLE','INDEX') and extents>20
order by owner,segment_name;