Pavan DBA's Blog

The DBA Knowledge Store

Posts Tagged ‘script to check free space in a datafile’

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;

Posted in Administration, Scripts | Tagged: , | 3 Comments »

 
%d bloggers like this: