Monitoring Temporary tablespace usage using shell script

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

select a.tablespace_name tablespace,
         d.TEMP_TOTAL_MB,
         sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_USED_MB,
         d.TEMP_TOTAL_MB - sum (a.used_blocks * d.block_size) / 1024 / 1024 TEMP_FREE_MB
from v$sort_segment a,
        (
          select   b.name, c.block_size, sum (c.bytes) / 1024 / 1024 TEMP_TOTAL_MB
          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.TEMP_TOTAL_MB;

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=catdb
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!" shashidba1208@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

NOTE:- Please give proper permission to check_temp_tbs.sql and alert_temp.sh if require.
P.S.Make sure mailx rpm is installed and any  mailing service such as sendmail/postfix is running on the server.