Oracle Tablespace Usage Script - Used and Free

set pagesize 100

column file_name format a32
column tablespace_name format a15
column status format a3 trunc
column t format 999,999.000 heading "Total MB"
column a format a4 heading "Aext"
column p format 990.00 heading "% Free"

SELECT df.file_name,
df.tablespace_name,
df. status,
(df.bytes/1024000) t,
(fs.s/df.bytes*100) p,
decode (ae.y,1,'YES','NO') a
FROM dba_data_files df,
(SELECT file_id,SUM(bytes) s
FROM dba_free_space
GROUP BY file_id) fs,
(SELECT file#, 1 y
FROM sys.filext$
GROUP BY file#) ae
WHERE df.file_id = fs.file_id
AND ae.file#(+) = df.file_id
ORDER BY df.tablespace_name, df.file_id;