Email Generation Shell Scripts for DB Monitoring

Tablespace Monitor

#!/bin/sh
. /d01/oracle/VIS/db/tech_st/11.1.0/VIS_oracle.env
cat /home/oracle/VISION/tablespace.log
sqlplus '/ as sysdba' << EOF
set feedback off echo off
set linesize 100
set pagesize 200
set colsep |
SET TRIMSPOOL ON
spool /home/oracle/VISION/tablespace.log
SELECT df.tablespace_name TABLESPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB, fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM (bytes) TOTAL_SPACE,
ROUND( SUM (bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM (bytes) FREE_SPACE, ROUND( SUM (bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY ROUND(100 * (fs.free_space / df.total_space),2);
spool off
exit
EOF
if [ `cat /home/oracle/VISION/tablespace.log|wc -l` -gt 0 ]
then
cat  /home/oracle/VISION/tablespace.log >> /home/oracle/VISION/mailcontent
cat /home/oracle/VISION/tablespace.log |mailx -s "Tablespace Monitor" amila@aitkenspence.lk
rm -rf /home/oracle/VISION/tablespace.log
rm -rf /home/oracle/VISION/mailcontent
fi

Temporary Tablespace Monitor 

#!/bin/sh
. /d01/oracle/VIS/db/tech_st/11.1.0/VIS_oracle.env
cat /home/oracle/VISION/tablespace.log
sqlplus '/ as sysdba' << EOF
set feedback off echo off
set linesize 100
set pagesize 200
set colsep |
SET TRIMSPOOL ON
spool /home/oracle/VISION/tablespace.log
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v\$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v\$tablespace B, v\$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
spool off
exit
EOF
if [ `cat /home/oracle/VISION/tablespace.log|wc -l` -gt 0 ]
then
cat  /home/oracle/VISION/tablespace.log >> /home/oracle/VISION/mailcontent
cat /home/oracle/VISION/tablespace.log |mailx -s "Temporary Tablespace Monitor" amila@aitkenspence.lk
rm -rf /home/oracle/VISION/tablespace.log
rm -rf /home/oracle/VISION/mailcontent
fi


Invalid Objects Monitor
 
#!/bin/sh
. /d01/oracle/VIS/db/tech_st/11.1.0/VIS_oracle.env
cat /home/oracle/VISION/invalidobj.log
sqlplus '/ as sysdba' << EOF
set feedback off echo off
set linesize 100
set pagesize 200
set colsep |
column OBJECT_NAME format 999999
column OBJECT_NAME format a30
spool /home/oracle/VISION/invalidobj.log
SELECT  OWNER, OBJECT_NAME , OBJECT_TYPE "ObjType"
FROM    DBA_OBJECTS
WHERE   STATUS = 'INVALID'
ORDER BY OWNER, OBJECT_TYPE, OBJECT_NAME;
spool off
exit
EOF
if [ `cat /home/oracle/VISION/invalidobj.log|wc -l` -gt 0 ]
then
cat  /home/oracle/VISION/invalidobj.log >> /home/oracle/VISION/mailcontentobj
cat /home/oracle/VISION/invalidobj.log |mailx -s "Invalid Objects" amila@aitkenspence.lk
rm -rf /home/oracle/VISION/invalidobj.log
rm -rf /home/oracle/VISION/mailcontentobj
fi