Monitoring Temporary tablespace usage using shell script

The following script will report if temporary tablespace  hits 80% of its usage.

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

set head off
set wrap off
select (s.tot_used_blocks/f.total_blocks)*100 as "percent used"
from (select sum(used_blocks) tot_used_blocks
from v$sort_segment where tablespace_name='TEMP') s,
(select sum(blocks) total_blocks
from dba_temp_files where tablespace_name='TEMP') f;
exit;


Now lets create the shell-script which will monitor the space usage of temp tablespaces:-
From oracle user

[oracle@server1 ~]$ vi /home/oracle/alert_temp.sh
#!/bin/bash
export ORACLE_SID=prim
sqlplus -S / as sysdba @/home/oracle/check_temp_tbs.sql > /home/oracle/temptbs.txt
space=`cat /home/oracle/temptbs.txt`
if [ $space -gt 80 ]; then
mail -s echo "Attention!! Low space in  Temporary 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_temp.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

Nenhum comentário:

Postar um comentário

Related Posts Plugin for WordPress, Blogger...