Pavan DBA's Blog

The DBA Knowledge Store

script to check tablespace free space for all db’s in server

Posted by Pavan DBA on May 24, 2012

script to check tablespace free space for all db’s in server


setenv ORATAB /var/opt/oracle/oratab
foreach x (`cat ${ORATAB} | grep -v “^#”| grep “^[a-z]” | grep -v “demo” |grep -v _sp | grep -v “test”| awk -F:

‘{print $1}’`)
        setenv ORAENV_ASK 1
        setenv ORACLE_SID “$x”
        source /usr/local/default/oracle_sid.csh
            sqlplus -s <<EOF
        / as sysdba
col total_space format 999,999,999,999
col free_space format 999,999,999,999
col pct_used format 999.99
col value new_value sid
set termout off
set head off
select value from v\$parameter where name=’db_name’;
set termout on
ttitle sid ” – Tablespaces Free space information” skip 2

set head on
set lines 234
set pages 100

SELECT /* + RULE */  df.tablespace_name “Tablespace”,
       df.bytes / (1024 * 1024) “Size (MB)”,
       round(SUM(fs.bytes) / (1024 * 1024)) “Free (MB)”,
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
       Round((df.bytes – SUM(fs.bytes)) / 1024/1024) “Used space”,
       Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
 order by  “% Used” desc

Note: This script is written in C shell. Do make changes if you are using other shell. Also, test it before…

4 Responses to “script to check tablespace free space for all db’s in server”

  1. the useful article read all parts one go thanks for this beautiful article oracle fusion procurement training

  2. dinesh dubey said

    Hi RULE there as a hint.

Leave a Reply

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

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

Facebook photo

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

Connecting to %s

%d bloggers like this: