#!/bin/bash
# This script is to send mail for alerting tablespace.
# Assumption smtb confirgured,TNS entry of db in tnsname.ora,created DIR's

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export PATH=/u01/app/oracle/product/11.2.0/db_1/bin:/usr/sbin:$PATH
export SCRIPT_DIR=/u01/app/oracle/util/scripts
export LOG_DIR=/u01/app/oracle/util/log
export TNS_ADMIN=/u01/app/oracle/util/scripts
CURR_DATE=`date '+%m/%d/%y_%H:%M'`

if [ -e ${LOG_DIR}/tbsp_max_status.txt ]
then
`rm ${LOG_DIR}/tbsp_max_status.txt `
fi
if [ -e ${LOG_DIR}/tbsp_max_output.log ]
then
`rm ${LOG_DIR}/tbsp_max_output.log `
fi

html_head="Subject: High Tablespace Alert for all DB on ${CURR_DATE}
\nMIME-Version: 1.0\nContent-Type: text/html\nContent-Disposition: inline\n
<html>

<head>
<meta http-equiv=\"Content-Type\" content=text/html; charset=\"UTF-8\">
<meta name=\"generator\" content=\"SQL*Plus 11.2.0\">
<style type=\"text/css\">
body {font:10pt Arial,Helvetica,sans-serif;
color:black; background:White;}
p {font:10pt Arial,Helvetica,sans-serif;
color:black; background:White;}
table,tr,td {font:10pt Arial,Helvetica,sans-serif;
color:Black; background:#f7f7e7;
padding:0px 0px 0px 0px; margin:0px 0px 0px 0px;}
th {font:bold 10pt Arial,Helvetica,sans-serif;
color:#336699; background:#cccc99; padding:0px 0px 0px 0px;}
h1 {font:13pt Arial,Helvetica,Geneva,sans-serif;
color:#336699;text-decoration:underline;background-color:White;
border-bottom:1px solid #cccc99;
margin-top:0pt; margin-bottom:0pt;
padding:0px 0px 0px 0px;}
h2 {font:bold 10pt Arial,Helvetica,Geneva,sans-serif;
color:#336699; background-color:White;
margin-top:4pt; margin-bottom:0pt;}
a {font:9pt Arial,Helvetica,sans-serif;
color:#663300; background:#ffffff;
margin-top:0pt; margin-bottom:0pt; vertical-align:top;}
</style>
<title>
Tablespace Alert on ${CURR_DATE}
</title>
</head>"
html_tail='<br><a><br>
<br> </a></body></html>'
reported_var=0
found_error=0
touch ${LOG_DIR}/tbsp_max_status.txt

echo "<br><br>" >> ${LOG_DIR}/tbsp_max_status.txt
#Enter the database's.
for SID in DBSID1 DBSID2 DBSID3
do
echo $SID
sqlplus -s username/password@${SID} << EOF
set feed off term off trims on linesize 300 pages 300 echo off underline off heading on
set markup html on
spool ${LOG_DIR}/tbsp_max_output.log
col tablespace_name for a30 heading "TABLESPACE_NAME"
col Allocated_size for 999,999,999,999 heading "ALLOCATED SIZE IN MB"
col Current_Size for 999,999,999,999 heading "CURRENT SIZE IN MB"
col USED_SIZE for 999,999,999,999 heading "USED SIZE IN MB"
col Available_size for 999,999,999,999,999 heading "AVAILABLE SIZE IN MB"
col Pct_used for a40 heading "Percentage Used" entmap off

select a.tablespace_name
,a.alloc_size/1024/1024 Allocated_size
,a.cur_size/1024/1024 Current_Size
,(U.USED+a.FILE_COUNT*65536)/1024/1024 USED_SIZE
,(a.alloc_size-(u.used + a.file_count*65536))/1024/1024 Available_size
,case
when ((u.used+a.file_count*65536)*100)/a.alloc_size >= 90 and ((u.used+a.file_count*65536)*100)/a.alloc_size < 95
then '<font color="#FF7F26"> '||round(((u.used+a.file_count*65536)*100)/a.alloc_size,2)||'</font>'
else
'<font color="red">'||round(((u.used+a.file_count*65536)*100)/a.alloc_size,2)||'</font>'
end Pct_used
from dba_tablespaces t
,(select t1.tablespace_name
,nvl(sum(s.bytes),0) used
from dba_segments s
,dba_tablespaces t1
where t1.tablespace_name=s.tablespace_name(+)
group by t1.tablespace_name) u
,(select d.tablespace_name
,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
,sum(d.bytes) cur_size
,count(*) file_count
from dba_data_files d
group by d.tablespace_name) a
where t.tablespace_name=u.tablespace_name
and t.tablespace_name=a.tablespace_name
and ((u.used+a.file_count*65536)*100)/a.alloc_size >= 90;
spool off
exit
EOF

reported_var=`grep 'ALLOCATED SIZE IN MB' ${LOG_DIR}/tbsp_max_output.log | wc -l`
if test ${reported_var} -gt 0
then
found_error=1
echo "<br><h1>THE Tablespace report for the Database: ${SID} </h1><br>" >> ${LOG_DIR}/tbsp_max_status.txt
cat ${LOG_DIR}/tbsp_max_output.log |grep -v 'SQL>'|grep -v 'SQL&gt' >> ${LOG_DIR}/tbsp_max_status.txt
fi
done
if [ -e ${LOG_DIR}/Tablespace_Max_REPORT.html ]
then
`rm ${LOG_DIR}/Tablespace_Max_REPORT.html `
fi

echo -e $html_head > ${LOG_DIR}/Tablespace_Max_REPORT.html
cat ${LOG_DIR}/tbsp_max_status.txt >> ${LOG_DIR}/Tablespace_Max_REPORT.html
echo $html_tail >> ${LOG_DIR}/Tablespace_Max_REPORT.html
rm ${LOG_DIR}/tbsp_max_output.log
rm ${LOG_DIR}/tbsp_max_status.txt
if test $found_error -gt 0
then
sendmail -v username@mailid.com < ${LOG_DIR}/Tablespace_Max_REPORT.html
fi

########
Out put

THE Tablespace report for the Database: DBSID1

TABLESPACE_NAME	ALLOCATED SIZE IN MB	CURRENT SIZE IN MB	USED SIZE IN MB	AVAILABLE SIZE IN MB	Percentage Used
DATA	371,712	371,648	334,657	37,055	90.03