How to check tablespace free space with a visual bar in Oracle

Account for autoextend: max_free_mb is the free space you would have if every datafile grew to its max size. The RPAD bar gives a quick eyeball of how full each tablespace is without switching to a GUI.

SET PAGESIZE 140 LINESIZE 200
COLUMN used_pct FORMAT a11

SELECT tablespace_name,
       size_mb,
       free_mb,
       max_size_mb,
       max_free_mb,
       TRUNC((max_free_mb / max_size_mb) * 100) AS free_pct,
       RPAD(' ' || RPAD('X', ROUND((max_size_mb - max_free_mb) / max_size_mb * 10, 0), 'X'), 11, '-') AS used_pct
FROM (
  SELECT a.tablespace_name,
         b.size_mb,
         a.free_mb,
         b.max_size_mb,
         a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
  FROM   (SELECT tablespace_name,
                 TRUNC(SUM(bytes) / 1024 / 1024) AS free_mb
          FROM   dba_free_space
          GROUP BY tablespace_name) a,
         (SELECT tablespace_name,
                 TRUNC(SUM(bytes) / 1024 / 1024) AS size_mb,
                 TRUNC(SUM(GREATEST(bytes, maxbytes)) / 1024 / 1024) AS max_size_mb
          FROM   dba_data_files
          GROUP BY tablespace_name) b
  WHERE  a.tablespace_name = b.tablespace_name
)
ORDER BY tablespace_name;