How to check TEMP tablespace free space per datafile in Oracle

Per-tempfile view: current MB, free MB, max size (accounting for autoextend), max free (headroom at max size), and an ASCII bar showing used-percentage. Useful when TEMP fills up during heavy sorts or large hash joins.

SET LINESIZE 255
COLUMN tablespace_name FORMAT a20
COLUMN file_name       FORMAT a40

SELECT tf.tablespace_name,
       tf.file_name,
       tf.size_mb,
       f.free_mb,
       tf.max_size_mb,
       f.free_mb + (tf.max_size_mb - tf.size_mb) AS max_free_mb,
       RPAD(' ' || RPAD('X',
            ROUND((tf.max_size_mb - (f.free_mb + (tf.max_size_mb - tf.size_mb))) / tf.max_size_mb * 10, 0),
            'X'), 11, '-') AS used_pct
FROM   (SELECT file_id, file_name, tablespace_name,
               TRUNC(bytes / 1024 / 1024) AS size_mb,
               TRUNC(GREATEST(bytes, maxbytes) / 1024 / 1024) AS max_size_mb
        FROM   dba_temp_files) tf,
       (SELECT file_id,
               TRUNC(SUM(bytes) / 1024 / 1024) AS free_mb
        FROM   dba_free_space
        GROUP BY file_id) f
WHERE  tf.file_id = f.file_id (+)
ORDER BY tf.tablespace_name, tf.file_name;