col total for 999,999,999
col free for 999,999,999
col used for 999,999,999
col p_used for 999
col name for a15
col nf for 99
col status for a7
col lmt for a7
SELECT 
	d.tablespace_name name, 
	NVL(a.bytes / 1024 / 1024, 0) total, 
	DECODE(d.contents,'UNDO', 
 	    NVL(u.bytes, 0)/1024/1024, 
	    NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024) used, 
	DECODE(d.contents,'UNDO', 
	    NVL(u.bytes / a.bytes * 100, 0), 
	    NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0)) p_used, 
	DECODE(d.contents,'UNDO', 
	    NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024 , 
	    NVL(f.bytes, 0) / 1024 / 1024) free, 
	d.status, 
	a.count nf, 
	d.contents, 
	d.extent_management LMT, 
	d.segment_space_management 
FROM 
	sys.dba_tablespaces d, 
	(SELECT 
	        tablespace_name, 
        	SUM(bytes) bytes, 
        	COUNT(file_id) count 
	from 
        	dba_data_files 
	GROUP BY 
        	tablespace_name) a, 
	(select 
        	tablespace_name, 
        	sum(bytes) 
        	bytes 
        from 
   	        dba_free_space 
	group by tablespace_name) f, 
	(SELECT 
	        tablespace_name, 
	        SUM(bytes) bytes 
	FROM 
        	dba_undo_extents 
	WHERE 
  	         status IN ('ACTIVE','UNEXPIRED') 
	GROUP BY tablespace_name) u 
	WHERE 
	d.tablespace_name = a.tablespace_name(+) AND 
	d.tablespace_name = f.tablespace_name(+) AND 
	d.tablespace_name = u.tablespace_name(+) AND 
	NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY') 
UNION ALL 
	SELECT d.tablespace_name, 
	NVL(a.bytes / 1024 / 1024, 0), 
	NVL(t.bytes, 0)/1024/1024, 
	NVL(t.bytes / a.bytes * 100, 0), 
	(NVL(a.bytes ,0)/1024/1024 - NVL(t.bytes, 0)/1024/1024), 
	d.status, a.count, 
	d.contents, 
	d.extent_management, 
	d.segment_space_management 
	FROM 
	sys.dba_tablespaces d, 
	(select 
	tablespace_name, 
	sum(bytes) bytes, 
	count(file_id) count 
	from 
	dba_temp_files 
	group by tablespace_name) a, 
	(select 
	ss.tablespace_name , 
	sum((ss.used_blocks*ts.blocksize)) bytes 
	from 
	gv$sort_segment ss, 
	sys.ts$ ts 
	where 
	ss.tablespace_name = ts.name 
	group by ss.tablespace_name) t 
	WHERE d.tablespace_name = a.tablespace_name(+) AND 
	d.tablespace_name = t.tablespace_name(+) AND 
	d.extent_management = 'LOCAL' AND 
	d.contents = 'TEMPORARY' 
	ORDER BY 1
/