Pavan DBA's Blog

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

#!/bin/csh

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
/
        exit
EOF
end

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