Tablespace Usage

SELECT d.status,
       d.tablespace_name,
       d.contents TYPE,
       d.extent_management extent_mgt,
       d.segment_space_management segment_mgt,
       NVL (a.bytes, 0) ts_size,
       NVL (f.bytes, 0) free,
       NVL (a.bytes - NVL (f.bytes, 0), 0) used,
       TO_CHAR (
          TRUNC (NVL ( (a.bytes - NVL (f.bytes, 0)) / a.bytes * 100, 0)))
          pct_used
  FROM sys.dba_tablespaces d,
       (  SELECT tablespace_name, SUM (bytes) bytes
            FROM dba_data_files
        GROUP BY tablespace_name) a,
       (  SELECT tablespace_name, SUM (bytes) bytes
            FROM dba_free_space
        GROUP BY tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = f.tablespace_name(+)
       AND NOT (d.extent_management LIKE 'LOCAL'
                AND d.contents LIKE 'TEMPORARY')
UNION ALL
SELECT d.status,
       d.tablespace_name,
       d.contents TYPE,
       d.extent_management extent_mgt,
       d.segment_space_management segment_mgt,
       NVL (a.bytes, 0) ts_size,
       NVL (a.bytes - NVL (t.bytes, 0), 0) free,
       NVL (t.bytes, 0) used,
       TO_CHAR (TRUNC (NVL (t.bytes / a.bytes * 100, 0))) pct_used
  FROM sys.dba_tablespaces d,
       (  SELECT tablespace_name, SUM (bytes) bytes
            FROM dba_temp_files
        GROUP BY tablespace_name) a,
       (  SELECT tablespace_name, SUM (bytes_cached) bytes
            FROM v$temp_extent_pool
        GROUP BY tablespace_name) t
 WHERE     d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = t.tablespace_name(+)
       AND d.extent_management LIKE 'LOCAL'
       AND d.contents LIKE 'TEMPORARY'
ORDER BY 9 DESC;