Shell script for Tablespace alert via email

for sid in `ps -ef | grep pmon | awk '{print $8}' | egrep -i "ora_pmon" | sed 's/ora_pmon_//'`

do

DB=${sid}

ORAENV_ASK=NO

ORACLE_SID=$DB

. /u01/oracle/bin/oraenv > /dev/null 2>&1

ORAENV_ASK=YES

> output.log

> output1.log



sqlplus -s '/ as sysdba' << EOF
SET LINESIZE 130 PAGES 0 verify off heading off feedback off
COLUMN TABLESPACE FORMAT A25
spool output.log
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-fs.freespace),2) as "Used Space(MB)",
fs.freespace as "Freespace(MB)",
round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used",
round((fs.freespace/t.totalspace)*100,2) as "% Free"
from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs
where t.tablespace=fs.tablespace and round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) > 90
and t.tablespace not in ('SYSTEM','SYSAUX')
order by t.tablespace;
spool off;

SET LINESIZE 130 PAGES 10 heading on feedback on
spool output1.log
select t.tablespace, t.totalspace as " Totalspace(MB)",
round((t.totalspace-fs.freespace),2) as "Used Space(MB)",
fs.freespace as "Freespace(MB)",
round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used",
round((fs.freespace/t.totalspace)*100,2) as "% Free"
from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t,
(select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs
where t.tablespace=fs.tablespace and round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) > 90
and t.tablespace not in ('SYSTEM','SYSAUX')
order by t.tablespace;
spool off;
exit
EOF
done

if [ `cat output.log | wc -c` -gt 0 ] ; then mailx -s "$DB@`hostname` DB TS info" email@com < output1.log ; fi

Getting TNS strings from tnsnames.ora file in Linux

grep '^[A-Za-z]' tnsnames.ora | sed 's/=.*//;s/,.*//'