Pavan DBA's Blog

The DBA Knowledge Store

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;

Advertisements

3 Responses to “script to check free space in a datafile”

  1. 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

  2. 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

  3. ramesh dayaka said

    Marvellous sir

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: