ts_free_space.sql

select
	tablespace_name,
	curr_size,
	max_size,
	free_size,
	curr_size-free_size used_size,
	pct_free,
	round(((max_size-(curr_size-free_size))/max_size)*100,2) pct_free_total,
	free_chunks,
	largest_chunk
from
	(select 
		ts.tablespace_name,
		round(dbf.bytes/1024/1024,2) curr_size,
		round(dbf.maxbytes/1024/1024) max_size,
		nvl(round(fs.bytes/1024/1024,2),0) free_size,
		round((nvl(fs.bytes,0)/dbf.bytes)*100,2) pct_free,
		nvl(fs.free_chunks,0) free_chunks,
		nvl(round(fs.largest_chunk/1024/1024,2),0) largest_chunk
	 from
		dba_tablespaces ts,
		(select
			tablespace_name,
			sum(bytes) bytes, 
			sum(greatest(maxbytes,bytes)) maxbytes
		 from
			(select tablespace_name,bytes,maxbytes from dba_data_files)
		 group by tablespace_name
		) dbf,
		(select
			tablespace_name, 
			sum(bytes) bytes,
			count(*) free_chunks,
			max(bytes) largest_chunk
		 from dba_free_space
		 group by tablespace_name
		) fs
	 where ts.tablespace_name=dbf.tablespace_name
	   and ts.tablespace_name=fs.tablespace_name(+)
	)
order by pct_free desc;