SELECT df.CON_NAME,
       df.TABLESPACE_NAME                  AS TABLESPACE,
       df.CONTENTS                         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,
       NVL(SUM(df.BYTES) - SUM(f.FREE), 0) AS USED_BYTES,
       ROUND(CASE SUM(df.MAX_BYTES)
                 WHEN 0 THEN 0
                 ELSE (SUM(df.BYTES) / SUM(df.MAX_BYTES) * 100)
                 END, 2)                   AS USED_PCT_MAX,
       ROUND(CASE SUM(df.BYTES)
                 WHEN 0 THEN 0
                 ELSE (NVL(SUM(df.BYTES) - SUM(f.FREE), 0)) / SUM(df.BYTES) * 100
                 END, 2)                   AS USED_FILE_PCT,
       ROUND(CASE SUM(df.MAX_BYTES)
                 WHEN 0 THEN 0
                 ELSE NVL(SUM(df.BYTES) - SUM(f.FREE), 0) / SUM(df.MAX_BYTES) * 100
                 END, 2)                   AS USED_FROM_MAX_PCT,
       CASE df.STATUS
           WHEN 'ONLINE' THEN 1
           WHEN 'OFFLINE' THEN 2
           WHEN 'READ ONLY' THEN 3
           ELSE 0
           END                             AS STATUS
FROM (SELECT ct.CON$NAME                              AS CON_NAME,
             cdf.FILE_ID,
             ct.CONTENTS,
             ct.STATUS,
             cdf.FILE_NAME,
             cdf.TABLESPACE_NAME,
             TRUNC(cdf.BYTES)                         AS BYTES,
             TRUNC(GREATEST(cdf.BYTES, cdf.MAXBYTES)) AS MAX_BYTES
      FROM CDB_DATA_FILES cdf,
           CDB_TABLESPACES ct
      WHERE cdf.TABLESPACE_NAME = ct.TABLESPACE_NAME
        AND cdf.CON_ID = ct.CON_ID
        AND ((ct.CON$NAME = '{#CON_NAME}') or (ct.CON$NAME is null and ct.CON_ID = 0))
        AND cdf.TABLESPACE_NAME = '{#TABLESPACE}'
        AND ct.CONTENTS = '{#CONTENTS}') df,
     (SELECT TRUNC(SUM(BYTES)) AS FREE,
             FILE_ID
      FROM CDB_FREE_SPACE
      GROUP BY FILE_ID) f
WHERE df.FILE_ID = f.FILE_ID (+)
GROUP BY df.CON_NAME,
         df.TABLESPACE_NAME,
         df.CONTENTS,
         df.STATUS
UNION ALL
SELECT Y.CON_NAME,
       Y.NAME                                   AS TABLESPACE,
       Y.CONTENTS                               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_BYTES,
       NVL(SUM(Y.BYTES) - MAX(Y.FREE_BYTES), 0) AS USED_BYTES,
       ROUND(CASE SUM(Y.MAX_BYTES)
                 WHEN 0 THEN 0
                 ELSE (SUM(Y.BYTES) / SUM(Y.MAX_BYTES) * 100)
                 END, 2)                                    AS USED_PCT_MAX,
       ROUND(CASE SUM(Y.BYTES)
                 WHEN 0 THEN 0
                 ELSE (NVL(SUM(Y.BYTES) - MAX(Y.FREE_BYTES), 0)) / SUM(Y.BYTES) * 100
                 END, 2)                                    AS USED_FILE_PCT,
       ROUND(CASE SUM(Y.MAX_BYTES)
                 WHEN 0 THEN 0
                 ELSE (NVL(SUM(Y.BYTES) - MAX(Y.FREE_BYTES), 0)) / SUM(Y.MAX_BYTES) * 100
                 END, 2)                                    AS USED_FROM_MAX_PCT,
       CASE Y.STATUS
           WHEN 'ONLINE' THEN 1
           WHEN 'OFFLINE' THEN 2
           WHEN 'READ ONLY' THEN 3
           ELSE 0
           END                                  AS STATUS
FROM (SELECT ct.CON$NAME                  AS CON_NAME,
             ctf.TABLESPACE_NAME          AS NAME,
             ct.CONTENTS,
             ctf.STATUS                   AS STATUS,
             ctf.BYTES                    AS BYTES,
             (SELECT ((f.TOTAL_BLOCKS - s.TOT_USED_BLOCKS) * vp.VALUE)
              FROM (SELECT CON_ID,
                           TABLESPACE_NAME,
                           SUM(USED_BLOCKS) TOT_USED_BLOCKS
                    FROM V$SORT_SEGMENT
                    WHERE TABLESPACE_NAME != 'DUMMY'
                    GROUP BY CON_ID,
                             TABLESPACE_NAME) s,
                   (SELECT CON_ID,
                           TABLESPACE_NAME,
                           SUM(BLOCKS) TOTAL_BLOCKS
                    FROM CDB_TEMP_FILES
                    WHERE TABLESPACE_NAME != 'DUMMY'
                    GROUP BY CON_ID,
                             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 = ctf.TABLESPACE_NAME
                AND f.CON_ID = s.CON_ID
                AND f.CON_ID = ct.CON_ID) AS FREE_BYTES,
             CASE
                 WHEN ctf.MAXBYTES = 0 THEN ctf.BYTES
                 ELSE ctf.MAXBYTES
                 END                      AS MAX_BYTES
      FROM CDB_TEMP_FILES ctf,
           CDB_TABLESPACES ct
      WHERE ctf.TABLESPACE_NAME = ct.TABLESPACE_NAME
        AND ctf.CON_ID = ct.CON_ID
        AND ((ct.CON$NAME = '{#CON_NAME}') or (ct.CON$NAME is null and ct.CON_ID = 0))
        AND ctf.TABLESPACE_NAME = '{#TABLESPACE}'
        AND ct.CONTENTS = '{#CONTENTS}') Y
GROUP BY Y.CON_NAME,
         Y.NAME,
         Y.CONTENTS,
         Y.STATUS;