Sel tbls

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
/