***************************************************
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…
Pavan DBA's Blog 