REM ************************************************************************
REM Filename:	DB_Space.sql    
REM Description:
REM	This script displays information about space in  
REM	tablespaces, fragmentation and potential  
REM	extent-problems. It is divided in 3: 
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		the number of free segments. 
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		into their tablespace. 
REM Comments:
REM
REM Modifications:
REM Author		Date	  Reason
REM Darryl B. Smith	09/01/97  Created
REM ************************************************************************
set pagesize 300 
set linesize 120
column sumb format 999,999,999,999 
column extents forma 9999 
column bytes format 999,999,999,999 
column largest format 999,999,999 
column nextext 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 
column Chunks_Free format 99,999  
column Max_Free format 999,999,999,999 
set echo off 
spool Space_Report.lst
 
PROMPT Space Available  
 
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) 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   Objects with more than 15 extents  
 
select owner,segment_name,extents,bytes ,  
	max_extents,next_extent  
from  dba_segments  
where segment_type in ('TABLE','INDEX') and extents > 15  
order by owner,segment_name; 
 
 
 
PROMPT Objects with no space left to grow  
 
select  a.owner, a.segment_name, b.tablespace_name, 
        decode(ext.extents,1,b.next_extent, 
        a.bytes*(1+b.pct_increase/100)) nextext,  
        freesp.largest 
from    dba_extents a, 
	dba_segments b, 
	(select owner, segment_name, max(extent_id) extent_id, 
		count(*) extents  
	from dba_extents  
	group by owner, segment_name) ext, 
	(select tablespace_name, max(bytes) largest 
	from dba_free_space  
	group by tablespace_name) freesp 
where   a.owner=b.owner and 
        a.segment_name=b.segment_name and 
        a.owner=ext.owner and  
        a.segment_name=ext.segment_name and 
        a.extent_id=ext.extent_id and 
        b.tablespace_name = freesp.tablespace_name and  
        decode(ext.extents,1,b.next_extent, 
               a.bytes*(1+b.pct_increase/100)) > freesp.largest 
/ 
 
 
spool off