Datafile Usage on an Oracle Database

Two useful queries to get datafile usage on an Oracle database

SELECT SUBSTR (df.tablespace_name, 1, 20) "Tablespace Name",
       SUBSTR (df.file_name, 1, 40) "File Name",
       ROUND (df.bytes / 1024 / 1024, 2) "Size (M)",
       ROUND (e.used_bytes / 1024 / 1024, 2) "Used (M)",
       ROUND (f.free_bytes / 1024 / 1024, 2) "Free (M)",
       RPAD (' ' || RPAD ('X', ROUND (e.used_bytes * 10 / df.bytes, 0), 'X'),
             11,
             '-')
           "% Used"
  FROM dba_data_files df,
       (SELECT file_id, SUM (DECODE (bytes, NULL, 0, bytes)) used_bytes
          FROM dba_extents
        GROUP BY file_id) e,
       (SELECT MAX (bytes) free_bytes, file_id
          FROM dba_free_space
        GROUP BY file_id) f
 WHERE e.file_id = df.file_id AND df.file_id = f.file_id
ORDER BY df.tablespace_name, df.file_name;

SELECT SUBSTR (df.name, 1, 40) file_name,
       df.bytes / 1024 / 1024 allocated_mb,
       ( (df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))
           used_mb,
       NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_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;