/*  
   This script displays information about space in  
   tablespaces, fragmentation and potential  
   extent-problems. It is divided in 3: 
	  
   1. Space available per tablespace.  
      Shows total size in bytes, total free space in bytes,  
      percentage free space to total space, 
      the size of the largest contigous free segment and  
      the number of free segments. 
   
   2. Lists The Extents > 50M
			      
   3. Lists tables and indexes with more than 200,to 5 extents. 
					  
   4. Lists tables/indexes whose next extent will not fit
      into their tablespace. 
*/ 
SET PAGESIZE 999; 
SET LINESIZE 132;
						    
column sumb format        999,999,999,999 
column extents format               9,999 
column max_extents format 999,999,999,999 
column next_extent format 999,999,999,999 
column nextext format     999,999,999,999 
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         999,999  
column Max_Free format    999,999,999,999 
column owner format        a15 
column segment_name format a30 
column segment_type format a8 
set echo off 
spool space.lis

break on today 
column today new_value _date
select to_char(SYSDATE, 'fmMonth DD, YYYY') today from dual;
clear breaks

PROMPT  Space available in tablespaces grouped by tablespace name
						      
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;

PROMPT   SEGMENTS WITH MORE THAN 50M NEXT EXTENTS
REM Added by Ed Miller

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

PROMPT   SEGMENTS WITH MORE THAN 200 EXTENTS ALLOCATED
REM Added by Ed Miller

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

PROMPT   SEGMENTS WITH MORE THAN 175 EXTENTS ALLOCATED
REM Added by Ed Miller

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

PROMPT   SEGMENTS WITH MORE THAN 150 EXTENTS ALLOCATED
REM Added by Ed Miller

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

PROMPT   SEGMENTS WITH MORE THAN 125 EXTENTS ALLOCATED
REM Added by Ed Miller

select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type  
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=125 and extents<150)
order by owner,segment_name; 
					       
PROMPT   SEGMENTS WITH MORE THAN 115 EXTENTS ALLOCATED
PROMPT   Things are really getting critical with the following segments.
PROMPT   The following segments are reaching their final extent allocation.
PROMPT   Remember that pre-version 7.2 databases only have 121 extents possible.

select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type  
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=115 and extents<125)
order by owner,segment_name; 
									
PROMPT   SEGMENTS WITH MORE THAN 105 EXTENTS ALLOCATED
PROMPT   The following segments are reaching their final extent allocation.
PROMPT   Remember that pre-version 7.2 databases only have 121 extents possible.

select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=105 and extents<115)
order by owner,segment_name; 
									 
PROMPT   Segments with more than 95 extents allocated
								  
select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=95 and extents<105) 
order by owner,segment_name; 
									   
PROMPT   Segments with more than 80 extents allocated
									    
select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=80 and extents<95)
order by owner,segment_name; 
									     
								      
PROMPT   Segments with more than 40 extents allocated
									       
select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=40 and extents<80)
order by owner,segment_name; 
										
PROMPT   Segments with more than 20 extents allocated
									 
select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=20 and extents<40)
order by owner,segment_name; 
									  
PROMPT   Segments with more than 10 extents allocated
									   
select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=10 and extents<20)
order by owner,segment_name; 
									    
PROMPT   Segments with more than 5 extents allocated
						     
select owner,segment_name,extents,bytes ,  
max_extents,next_extent,segment_type
from  dba_segments  
where segment_type in ('TABLE','INDEX') and (extents>=5 and extents<10)
order by owner,segment_name;  
									      
PROMPT Segments where there's not enough room for the next extent  
PROMPT Please add a datafile(s) to the tablespace to add additional room
PROMPT for growth.

select /*+ RULE */ a.owner, a.segment_name, b.tablespace_name,
next_extent,  
freesp.largest, b.segment_type 
from    dba_extents a, 
dba_segments b, 
(select /*+ RULE */ owner, segment_name, max(extent_id) extent_id, 
count(*) extents  
from dba_extents  
group by owner, segment_name 
) ext, 
(select /*+ RULE */ 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  
next_extent > freesp.largest
/ 
spool off;

exit