REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_tabsp.sql
REM
REM <SQLDIR_GRP>TABSP MOST</SQLDIR_GRP>
REM 
REM Author:
REM 	?? 
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Lists freespace by tablespace
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_tabsp.sql
REM 
REM Example:
REM	s_tabsp.sql
REM
REM
REM History:
REM	??-??-????	??		Created
REM	08-01-1999	VMOGILEV	Added to DBATOOLZ library
REM
REM

@x_db_name.sql

set pages 60

column  pct_used format 999.9       heading "%|Used" 
column  ts_name    format a21      heading "Tablespace Name" 
column  Kbytes   format 999,999,999    heading "KBytes" 
column  used    format 999,999,999   heading "Used" 
column  free    format 999,999,999  heading "Free" 
column  largest    format 999,999,999  heading "Largest" 
break   on report 
compute sum of kbytes on report 
compute sum of free on report 
compute sum of used on report 

select	nvl(b.tablespace_name,nvl(a.tablespace_name,'UNKNOWN'))	ts_name
,	kbytes_alloc						kbytes
,	kbytes_alloc-nvl(kbytes_free,0)				used
,	nvl(kbytes_free,0)					free
,	((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100	pct_used
,	nvl(largest,0)						largest
from	(select	sum(bytes)/1024			Kbytes_free
	,		max(bytes)/1024		largest
	,		tablespace_name
	from 		dba_free_space
	group by	tablespace_name)			a
,	(select		sum(bytes)/1024		Kbytes_alloc
	,		tablespace_name
	from		dba_data_files
	group by	tablespace_name)			b
where	a.tablespace_name (+) = b.tablespace_name
order by 5,1

spool s_tabsp.&db_name..&tstamp..log
/
spool off