SELECT JSON_ARRAYAGG(
         JSON_OBJECT(  tablespace_name VALUE
           JSON_OBJECT(
           'contents' VALUE contents,
           'file_bytes' VALUE file_bytes,
           'max_bytes' VALUE max_bytes,
           'free_bytes' VALUE free_bytes,
           'used_bytes' VALUE used_bytes,
           'used_pct_max' VALUE ROUND((used_bytes / max_bytes) * 100, 2),
           'used_file_pct' VALUE used_file_pct,
           'status' VALUE CASE status WHEN 'ONLINE' THEN 1 WHEN 'OFFLINE' THEN 2 WHEN 'READ ONLY' THEN 3 ELSE 0 END
         )
      RETURNING CLOB )
RETURNING CLOB ) AS JSON
FROM ( SELECT ts.tablespace_name ,
             ts.contents,
             size_info.file_bytes,
             size_info.max_bytes,
             size_info.free_bytes,
             size_info.used_bytes,
             size_info.used_file_pct,
             ts.status
      FROM (
            SELECT a.tablespace_name,
                   ROUND(a.bytes_alloc) AS file_bytes,
                   ROUND(NVL(b.bytes_free, 0)) AS free_bytes,
                   ROUND(a.bytes_alloc - NVL(b.bytes_free, 0)) AS used_bytes,
                   100 - ROUND((NVL(b.bytes_free, 0) / a.bytes_alloc) * 100) AS used_file_pct,
                   ROUND(maxbytes) AS max_bytes
            FROM (
                  SELECT f.tablespace_name,
                         SUM(f.bytes) AS bytes_alloc,
                         SUM(DECODE(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) AS maxbytes
                  FROM dba_data_files f
                  GROUP BY f.tablespace_name
                 ) a
            LEFT JOIN (
                       SELECT [ts.name|http://ts.name] AS tablespace_name, SUM(fs.blocks) * ts.blocksize AS bytes_free
                       FROM DBA_LMT_FREE_SPACE fs, sys.ts$ ts
                       WHERE ts.ts# = fs.tablespace_id
                       GROUP BY [ts.name|http://ts.name], ts.blocksize
                      ) b ON a.tablespace_name = b.tablespace_name
            UNION ALL
            SELECT h.tablespace_name,
                   ROUND(SUM(h.bytes_free + h.bytes_used)) AS file_bytes,
                   ROUND(SUM(h.bytes_free + h.bytes_used - NVL(p.bytes_used, 0))) AS free_bytes,
                   ROUND(SUM(NVL(p.bytes_used, 0))) AS used_bytes,
                   100 - ROUND((SUM(h.bytes_free + h.bytes_used - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100) AS used_file_pct,
                   ROUND(SUM(DECODE(f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes))) AS max_bytes
            FROM (SELECT DISTINCT * FROM sys.v_$TEMP_SPACE_HEADER) h
            JOIN sys.dba_temp_files f ON f.file_id = h.file_id AND f.tablespace_name = h.tablespace_name
            LEFT JOIN (SELECT DISTINCT * FROM sys.v_$TEMP_EXTENT_POOL) p ON p.file_id = h.file_id AND p.tablespace_name = h.tablespace_name
            GROUP BY h.tablespace_name
           ) size_info
      JOIN sys.dba_tablespaces ts ON ts.tablespace_name = size_info.tablespace_name
      LEFT JOIN sys.dba_tablespace_groups tsg ON ts.tablespace_name = tsg.tablespace_name
     )