Below is the shell script, to be configured in crontab, which will send mail incase of blocking session observed in the database .
In the mail body it will contain the blocking sessions details also.
1. Prepare the blocker.sql file.[ for blocking sessions more than 10 seconds)
set feed off
set pagesize 200
set lines 299
col event for a31
SELECT
s.inst_id,
s.blocking_session,
s.sid,
s.serial#,
s.seconds_in_wait,
s.event
FROM
gv$session s
WHERE
blocking_session IS NOT NULL and s.seconds_in_wait > 10;
2. Shell script.(/home/oracle/monitor/blocker.sh )
You need to define the ORACLE_HOME,ORACLE_SID respectively.
export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH
logfile=/home/oracle/monitor/block_alert.log
sqlplus -s "/as sysdba" > /dev/null << EOF
spool $logfile
@/home/oracle/monitor/blocker.sql
spool off
exit
EOF
count=`cat $logfile|wc -l`
if [ $count -ge 1 ];
then mailx -s "BLOCKING SESSION REPORTED IN PROD DB ( > 10 SEC) " [email protected] < $logfile
fi
3. configure in crontab( every one minute)
* * * * * /home/oracle/monitor/blocker.sh > /tmp/block.log
blocking SHELL SCRIPT