denrepo

OracleStorage

Datafile usage with a bar graph

Every datafile with its size, used space, max size and a ten-character usage bar (X = used, - = free). Free_MB is the largest free extent in the file, which is the biggest single chunk a new extent can take. Reads DBA_EXTENTS, so it can take a while on large databases.

Tested on 12c, 19c · verified Sep 2026

ora-datafile-usage-bar.sql 
1CLEAR COLUMNS2SET LINESIZE 700 PAGESIZE 200 TRIMOUT ON TAB OFF3COLUMN Tablespace_Name FORMAT A204COLUMN File_Name FORMAT A755 6SELECT Substr(df.tablespace_name,1,20) "Tablespace_Name",7CASE df.autoextensible8WHEN 'YES'9THEN 'YES'10ELSE 'NO'11END AS autoextensible,12Substr(df.file_name,1,75) "File_Name",13Round(df.bytes/1024/1024,2) "Size_MB",14Round(e.used_bytes/1024/1024,2) "Used_MB",15Round(f.free_bytes/1024/1024,2) "Free_MB",16Round(df.maxbytes/1024/1024,2) "Max_MB",17Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "%_Used"18FROM DBA_DATA_FILES DF,19(SELECT file_id,20Sum(Decode(bytes,NULL,0,bytes)) used_bytes21FROM dba_extents22GROUP by file_id) E,23(SELECT Max(bytes) free_bytes,24file_id25FROM dba_free_space26GROUP BY file_id) f27WHERE e.file_id (+) = df.file_id28AND df.file_id = f.file_id (+)29ORDER BY df.tablespace_name,30df.file_name;

Save it as ora-datafile-usage-bar.sql and run it with SQL> @ora-datafile-usage-bar.

Open in denrepo

More Oracle scripts: Storage