column  pct_used     format 999.9       heading "%|Used"
column  name         format a16         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"
column  max_size     format 999,999,999 heading "MaxPoss|Kbytes"
column  pct_max_used format 999.9       heading "%|Max|Used"
break   on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
prompt =================================================================
with
free_space_by_tablespace as
     ( select sum(bytes)/1024 Kbytes_free,
                          max(bytes)/1024 largest,
                          tablespace_name
           from  sys.dba_free_space
           group by tablespace_name ),
space_by_permanent_tablespace as
     ( select sum(bytes)/1024 Kbytes_alloc,
                          sum(maxbytes)/1024 Kbytes_max,
                          tablespace_name
           from sys.dba_data_files
           group by tablespace_name ),
space_by_temporary_tablespace as
     ( select sum(bytes)/1024 Kbytes_alloc,
                          sum(maxbytes)/1024 Kbytes_max,
                          tablespace_name
           from sys.dba_temp_files
           group by tablespace_name )
/* Now, finally to the query itself */
select b.tablespace_name 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,
           nvl(kbytes_max,kbytes_alloc) Max_Size,
           decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
  from free_space_by_tablespace a
           RIGHT OUTER JOIN
              space_by_permanent_tablespace b on a.tablespace_name = b.tablespace_name
union all
select b.tablespace_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,
           nvl(kbytes_max,kbytes_alloc) Max_Size,
           decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
  from free_space_by_tablespace a
           RIGHT OUTER JOIN
              space_by_temporary_tablespace b on a.tablespace_name = b.tablespace_name
/