select s.tablespace_name, s.max, s.counts, s.megs_alloc, s.megs_used, s.megs_free, round(1-((s.max-s.megs_used)/s.max),4)*100 as "used"
from
(SELECT a.tablespace_name,
       a.counts,
       ROUND (maxbytes / 1048576) MAX,
       ROUND (a.bytes_alloc / 1024 / 1024) megs_alloc,
       ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024) megs_used,
       ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) megs_free
  FROM (  SELECT f.tablespace_name,
                 count(f.file_id) as counts,
                 SUM (f.bytes) bytes_alloc,
                 SUM ( DECODE (f.autoextensible,'YES', f.maxbytes,'NO', f.bytes))maxbytes
            FROM dba_data_files f
        GROUP BY tablespace_name) a,
      (  SELECT f.tablespace_name, SUM (f.bytes) bytes_free
            FROM dba_free_space f
        GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name(+)
UNION ALL
  SELECT h.tablespace_name,
         count(f.file_id) as counts,
         ROUND (SUM (DECODE(f.autoextensible,'YES',f.maxbytes,'NO',f.bytes)) / 1048576) MAX,
         ROUND (SUM (h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
         ROUND (SUM (NVL (p.bytes_used, 0)) / 1048576) megs_used,
         ROUND (SUM ( (h.bytes_free + h.bytes_used) - NVL (p.bytes_used, 0))/ 1048576)megs_free
    FROM sys.v_$TEMP_SPACE_HEADER h,
         sys.v_$Temp_extent_pool p,
         dba_temp_files f
   WHERE     p.file_id(+) = h.file_id
         AND p.tablespace_name(+) = h.tablespace_name
         AND f.file_id = h.file_id
         AND f.tablespace_name = h.tablespace_name
GROUP BY h.tablespace_name ORDER BY 1  ) s
order by 7   desc