column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a33 heading "Tablespace Name"
column Mbytes format 9,999,999,999 heading "Mbytes"
column used format 9,999,999,999 heading "Used"
column free format 9,999,999,999 heading "Free"
break on report
compute sum of Mbytes on report
compute sum of free on report
compute sum of used on report
select nvl(b.tablespace_name, nvl(a.tablespace_name, 'UNKOWN')) name,
       mbytes_alloc mbytes,
       mbytes_alloc - nvl(mbytes_free, 0) used,
       nvl(mbytes_free, 0) free,
       ((mbytes_alloc - nvl(mbytes_free, 0)) / mbytes_alloc) * 100 pct_used
  from (select sum(bytes) / 1024 /1024  Mbytes_free ,tablespace_name from sys.dba_free_space group by tablespace_name) a,
       (select sum(bytes) / 1024 / 1024 Mbytes_alloc ,tablespace_name from sys.dba_data_files group by tablespace_name
        union all
        select sum(bytes) / 1024 / 1024 Mbytes_alloc ,tablespace_name from sys.dba_temp_files group by tablespace_name) b
 where a.tablespace_name(+) = b.tablespace_name
 order by 1
/


Tablespace Name                           Mbytes           Used           Free   Used
--------------------------------- -------------- -------------- -------------- ------
SYSAUX                                     2,460          2,293            167   93.2
SYSTEM                                     1,860          1,854              6   99.7
TEMP                                         173            173              0  100.0
UNDOTBS1                                     840             35            805    4.2
USERS                                          5              1              4   20.0
                                  -------------- -------------- --------------
sum                                        5,338          4,357            981

hostgator