set lines 1000
set pages 1000

column c1 heading "Tablespace"      format a25
column c2 heading "Allocated MB"    format 999999999999
column c3 heading "Used MB"         format 999999999999
column c4 heading "Free MB"         format 999999999999
column c5 heading "Used"            format 999999999999.00
column c6 heading "Data Files"      format 999

ACCEPT TABLESPACE_NAME CHAR DEFAULT '' PROMPT 'Enter tablespace name: '

define TABLESPACE_NAME='&TABLESPACE_NAME';

select nvl(b.tablespace_name, nvl(a.tablespace_name,'UNKNOWN')) c1,
	 kbytes_alloc c2,
	 kbytes_alloc-nvl(kbytes_free,0) c3,
	 nvl(kbytes_free,0) c4,
	 ((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc) c5,
	 data_files c6
from ( select sum(bytes)/1024/1024 Kbytes_free,
			  max(bytes)/1024/1024 largest,
			  tablespace_name
		 from sys.dba_free_space
		group by tablespace_name ) a,
	 ( select sum(bytes)/1024/1024 Kbytes_alloc,
			  tablespace_name,
			  count(*) data_files
		 from sys.dba_data_files
		group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
	and ('&TABLESPACE_NAME' is null or instr(lower(b.tablespace_name),lower('&TABLESPACE_NAME')) > 0)
order by 1							   
/