Show Used/Free Space Datafile wise in Oracle DB

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.

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;


Popular posts from this blog

How to create Oracle stored Procedures using TOAD for Oracle

Create Tables in Oracle Database using TOAD

How to Create Packages in Oracle Database using TOAD for Oracle