Script for Datafile usage in Oracle Database

This script will list datafile wise Allocated size, Used Size and Free Size

For running this query you must have SELECT privileges to V$DATAFILE and DBA_FREE_SPACE views or run the sript as sysdba

The Size details are displayed in MB (Mega Bytes)
-----------------------------------------------------------------------

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 "Allocated Size(MB)",
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) "Used Size (MB)",
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) "Free Size(MB)"
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;