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 = '{#TABLESPACE}'
          AND dt.contents = '{#CONTENTS}'
      ) 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 = '{#TABLESPACE}'
          AND dt.contents = '{#CONTENTS}'
      ) Y
GROUP BY Y.name, Y.TYPE, Y.tbs_status;