How to find out total allocated, Used and free space in all the datafiles in oracle

How to find out total allocated, Used and free space in all the datafiles in oracle

If you want to find out total allocated space, used space and free space available in a datafile please use below query:

SQL>SELECT SUBSTR (df.file_name, 1, 60) 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 dba_data_files df, dba_free_space dfs

WHERE df.FILE_ID = dfs.file_id(+)

GROUP BY dfs.file_id, df.file_NAME, df.file_id, df.bytes

UNION ALL

select file_name, bytes/1024/1024 "allocated_mb",user_bytes/1024/1024 "used_mb", ((bytes/1024/1024) - (user_bytes/1024/1024)) "free_space_mb" from dba_temp_files;

Output:

FILE_NAME                   ALLOCATED_MB    USED_MB FREE_SPACE_MB

-------------------------                    -----------------      --------------         -------------------

+BP_DATA/bplagp1/dat                 31744            29985                  1759

afile/lagoidx.269.88

9205885

+BP_DATA/bplagp1/dat                 24576            24576                     0

afile/lagodat.260.88

9205001

+BP_DATA/bplagp1/dat                31744            31526                     218

afile/lagoidx.274.88

9206041