SELECT
  df.tablespace_name AS TABLESPACE,
  df.type AS TYPE,
  NVL(SUM(df.BYTES), 0) AS FILE_BYTES,
  NVL(SUM(df.MAX_BYTES), 0) AS MAX_BYTES,
  NVL(SUM(f.FREE), 0) AS FREE_BYTES,
  SUM(df.BYTES)-SUM(f.FREE) AS USED_BYTES,
  ROUND(DECODE(SUM(df.MAX_BYTES), 0, 0, (SUM(df.BYTES) / SUM(df.MAX_BYTES) * 100)), 2) AS USED_PCT_MAX,
  ROUND(DECODE(SUM(df.BYTES), 0, 0,(SUM(df.BYTES)-SUM(f.FREE))/ SUM(df.BYTES)* 100), 2) AS USED_FILE_PCT,
  DECODE(df.STATUS, 'ONLINE', 1, 'OFFLINE', 2, 'READ ONLY', 3, 0) AS STATUS
FROM ( SELECT
   ddf.file_id,
   dt.contents AS TYPE,
   dt.STATUS ,
   ddf.file_name,
   ddf.tablespace_name,
   TRUNC(ddf.bytes) AS bytes,
   TRUNC(GREATEST(ddf.bytes, ddf.maxbytes)) AS max_bytes
   FROM
    dba_data_files ddf,
    dba_tablespaces dt
  WHERE
   ddf.tablespace_name = dt.tablespace_name ) df,
   ( SELECT TRUNC(SUM(bytes)) AS FREE, file_id FROM dba_free_space GROUP BY file_id ) f
   WHERE df.file_id = f.file_id (+)
   GROUP BY df.tablespace_name, df.TYPE, df.status
UNION ALL
SELECT
  Y.name AS TABLESPACE,
  Y.type AS TYPE,
  NVL(SUM(Y.BYTES), 0) AS FILE_BYTES,
  NVL(SUM(Y.MAX_BYTES), 0) AS MAX_BYTES,
  NVL(MAX(NVL(Y.FREE_BYTES, 0)), 0) AS FREE,
  SUM(Y.BYTES)-MAX(Y.FREE_BYTES) AS USED_BYTES,
  ROUND(DECODE(SUM(Y.MAX_BYTES), 0, 0, (SUM(Y.BYTES) / SUM(Y.MAX_BYTES) * 100)), 2) AS USED_PCT_MAX,
  ROUND(DECODE(SUM(Y.BYTES), 0, 0,(SUM(Y.BYTES)-MAX(Y.FREE_BYTES))/ SUM(Y.BYTES)* 100), 2) AS USED_FILE_PCT,
  DECODE(Y.TBS_STATUS, 'ONLINE', 1, 'OFFLINE', 2, 'READ ONLY', 3, 0) AS STATUS
   FROM ( SELECT
     dtf.tablespace_name AS name,
     dt.contents AS TYPE,
     dt.STATUS AS tbs_status,
     dtf.status AS status,
     dtf.bytes AS bytes,
     (SELECT
       ((f.total_blocks - s.tot_used_blocks)* vp.value)
       FROM ( SELECT tablespace_name, SUM(used_blocks) tot_used_blocks FROM gv$sort_segment
          WHERE tablespace_name != 'DUMMY'
          GROUP BY tablespace_name) s,
     ( SELECT tablespace_name, SUM(blocks) total_blocks FROM dba_temp_files
       WHERE tablespace_name != 'DUMMY'
       GROUP BY tablespace_name) f,
     ( SELECT value FROM v$parameter WHERE name = 'db_block_size') vp
   WHERE
     f.tablespace_name = s.tablespace_name
     AND f.tablespace_name = dtf.tablespace_name ) AS free_bytes,
     CASE WHEN dtf.maxbytes = 0 THEN dtf.bytes
     ELSE dtf.maxbytes END AS max_bytes
   FROM
    sys.dba_temp_files dtf,
    sys.dba_tablespaces dt
   WHERE
   dtf.tablespace_name = dt.tablespace_name ) Y
   GROUP BY Y.name, Y.TYPE, Y.tbs_status;