E-mail Generation for Daily Health Check Report via mailx OS utility

For e-mail generation we need SQL script and shell script files. SQL script extract data from DB to health check report and the shell script generate e-mail via mailx utility with sql output and then scheduled this for daily. 

SQL Scripts

[oracle@kiawiz ~]$ cat ts.sql
set feedback off echo off
set linesize 100
set pagesize 200
set colsep |
SET TRIMSPOOL ON
spool /home/oracle/tablespace.log
column tablespace_name format a25
column FreePC format a15
column FullPC format a15
select b.tablespace_name,round(tbs_size,0) SizeMb,round((tbs_size- a.free_space),0)UsedMb,round(a.free_space,0) FreeMb,to_char((round(((a.free_space*100)/tbs_size),2)),'00.00')||'%' FreePC,to_char((100-round(((a.free_space*100)/tbs_size),2)),'00.00')||'%' FullPC
from (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name
UNION
select tablespace_name,((free_space/1024)/1024) free_space
from dba_temp_free_space) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name=b.tablespace_name
order by 100-round(((a.free_space*100)/tbs_size),2) desc;

select count(*) Invalid_objects from dba_objects where status ='INVALID';

column object_name format a30
column owner format a15
select object_name,owner,object_type from dba_objects where status ='INVALID';
spool off
exit

Schell Script

[oracle@kiawiz ~]$ cat tablespace_new.sh
#!/bin/sh
. /finsys/db/tech_st/11.1.0/PROD_kiawiz.env
cat /home/oracle/tablespace.log
sqlplus '/ as sysdba' << EOF
@ts.sql;
EOF
if [ `cat /home/oracle/tablespace.log|wc -l` -gt 0 ]
then

echo -e "\n"  2>&1>>/home/oracle/tablespace.log
df -h 2>&1>>/home/oracle/tablespace.log

cat /home/oracle/tablespace.log |mailx -s "Production instance - Health Check" test@gmail.com
rm -rf /home/oracle/tablespace.log
fi

Schedule .sh file via crontab

00 09 * * 0-6 . /home/oracle/tablespace_new.sh