How to find tablespaces running out of space in Oracle

Permanent tablespaces at or above 80 percent used against their real ceiling, counting datafile autoextend max size rather than current allocation. The list to act on before something fails.

SELECT df.tablespace_name,
       ROUND(df.max_mb)                                   AS max_mb,
       ROUND(df.alloc_mb)                                 AS alloc_mb,
       ROUND(df.alloc_mb - NVL(fs.free_mb, 0))            AS used_mb,
       ROUND((df.alloc_mb - NVL(fs.free_mb,0)) / df.max_mb * 100, 1) AS pct_used_of_max
FROM (
  SELECT tablespace_name,
         SUM(bytes)/1024/1024 AS alloc_mb,
         SUM(GREATEST(bytes, DECODE(autoextensible,'YES',maxbytes,bytes)))/1024/1024 AS max_mb
  FROM   dba_data_files
  GROUP  BY tablespace_name
) df
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes)/1024/1024 AS free_mb
  FROM   dba_free_space
  GROUP  BY tablespace_name
) fs ON fs.tablespace_name = df.tablespace_name
WHERE  (df.alloc_mb - NVL(fs.free_mb,0)) / df.max_mb * 100 >= 80
ORDER  BY pct_used_of_max DESC;