free space calculation sql

SELECT SUBSTR (df.file_id, 1, 3) "ID#",

df.tablespace_name,

df.BYTES "Total Bytes",

df.blocks "SQL Blocks",

df.BYTES / 512 "VMS Blocks",

nvl(SUM (fs.BYTES),0) "Bytes Free",

nvl((100 * ((SUM (fs.BYTES)) / df.BYTES)),0) "% Free",

nvl(df.BYTES - SUM (fs.BYTES),df.BYTES) "Bytes Used",

nvl((100 * ((df.BYTES - SUM (fs.BYTES)) /

df.BYTES)),100) "% Used"

FROM SYS.dba_data_files df,

SYS.dba_free_space fs

WHERE df.file_id = fs.file_id (+)

GROUP BY df.file_id,

df.tablespace_name,

df.BYTES,

df.blocks

ORDER BY df.tablespace_name;