Monitoring Tablespace usage using shell script

The following script will report if any tablespace (except temporary tablespace ) hits 85% of its usage.


From oracle user:
[oracle@server1 ~]$ vi /home/oracle/checktbs.sql

select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 15
order by pct_free;
exit;

:wq(save & exit)


Now lets create the shell-script which will monitor the space usage of tablespaces:-
From oracle user
[oracle@server1 ~]$ vi /home/oracle/alert.sh
#!/bin/bash
export ORACLE_SID=prim
sqlplus -S / as sysdba @/home/oracle/checktbs.sql > /home/oracle/test.txt
file1=cat /home/oracle/test.txt
space=`cat /home/oracle/test.txt | wc -l`
if [ $space -gt 3 ]; then
$file1 | mail -s echo "Attention!! Low space Certain Tablespaces in $ORACLE_SID instance!" yourmailid@gmail.com
fi

:wq(save & exit)

We schedule the script which will check in every 5 mins.
[oracle@server1 ~]$ crontab -e
*/5 * * * * /home/oracle/alert.sh > /dev/null


P.S.Make sure mailx rpm is installed and any  mailing service such as sendmail/postfix is running on the server.
To  install sendmail:-
#yum -y install sendmail

To install mailx:-
#yum -y install mailx