script to check free space in a datafile
Posted by Pavan DBA on March 30, 2012
Friends, Many a times we will be requiring to resize a datafile. For suppose, we have a tablespace with 3 datafiles with equal size. Then how we would be knowing which datafile to increase?? It is based on free space in a datafile. So, the below script will provide the info of how much free space is left in each datafile for a tablespace
#################################################
DATAFILE FREE SIZE
#################################################
SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_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,dfs.tablespace_name
ORDER BY file_name;
Pavan DBA's Blog 
Muhammad Ahmad said
Another Useful Iteration to Ramesh Provided SQL –
SELECT SUBSTR (df.NAME, 1, 40) file_name,
dfs.tablespace_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,
(1-(df.bytes/1024/1024-NVL(SUM(dfs.bytes/1024/1024),0))/(df.bytes / 1024 / 1024))*100 “% Free”
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,dfs.tablespace_name
ORDER BY (1-(df.bytes/1024/1024-NVL(SUM(dfs.bytes/1024/1024),0))/(df.bytes / 1024 / 1024))*100;
Enjoy !
Regards,
Muhammad Ahmad
Muhammad Ahmad said
Thanks Ramesh, I have faced syntax error during copy / paste provided sql – Given below SQL is with corrections for friends …
SELECT SUBSTR (df.NAME, 1, 40) file_name,
dfs.tablespace_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,dfs.tablespace_name
ORDER BY file_name;
Regards,
-Muhammad Ahmad
ramesh dayaka said
Marvellous sir