How to check tablespace usage with autoextend max size in Oracle

Union of data and temp tablespaces: current bytes and free bytes side by side with max bytes (autoextend), and both current pct_used and max pct_used so you know whether you are running out today or would run out at max autoextend. Ordered by max_ts_pct_used descending.

SET PAGES 999 LINES 400

SELECT df.tablespace_name,
       MAX(df.autoextensible) AS auto_ext,
       ROUND(df.maxbytes / 1024 / 1024, 2) AS max_ts_size,
       ROUND((df.bytes - SUM(fs.bytes)) / df.maxbytes * 100, 2) AS max_ts_pct_used,
       ROUND(df.bytes / 1024 / 1024, 2) AS curr_ts_size,
       ROUND((df.bytes - SUM(fs.bytes)) / 1024 / 1024, 2) AS used_ts_size,
       ROUND((df.bytes - SUM(fs.bytes)) * 100 / df.bytes, 2) AS ts_pct_used,
       ROUND(SUM(fs.bytes) / 1024 / 1024, 2) AS free_ts_size,
       NVL(ROUND(SUM(fs.bytes) * 100 / df.bytes), 2) AS ts_pct_free
FROM   dba_free_space fs,
       (SELECT tablespace_name,
               SUM(bytes) AS bytes,
               SUM(DECODE(maxbytes, 0, bytes, maxbytes)) AS maxbytes,
               MAX(autoextensible) AS autoextensible
        FROM   dba_data_files
        GROUP BY tablespace_name) df
WHERE  fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name,
       MAX(df.autoextensible),
       ROUND(df.maxbytes / 1024 / 1024, 2),
       ROUND((df.bytes - SUM(fs.bytes)) / df.maxbytes * 100, 2),
       ROUND(df.bytes / 1024 / 1024, 2),
       ROUND((df.bytes - SUM(fs.bytes)) / 1024 / 1024, 2),
       ROUND((df.bytes - SUM(fs.bytes)) * 100 / df.bytes, 2),
       ROUND(SUM(fs.bytes) / 1024 / 1024, 2),
       NVL(ROUND(SUM(fs.bytes) * 100 / df.bytes), 2)
FROM   (SELECT tablespace_name, bytes_used AS bytes
        FROM   v$temp_space_header
        GROUP BY tablespace_name, bytes_free, bytes_used) fs,
       (SELECT tablespace_name,
               SUM(bytes) AS bytes,
               SUM(DECODE(maxbytes, 0, bytes, maxbytes)) AS maxbytes,
               MAX(autoextensible) AS autoextensible
        FROM   dba_temp_files
        GROUP BY tablespace_name) df
WHERE  fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;