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) " support@dbaclass.com < $logfile fi
3. configure in crontab( every one minute)
* * * * * /home/oracle/monitor/blocker.sh > /tmp/block.log
Hello.
Thanks for your example, but i have a bit problem, if my database doesn’t have any blocking sessions i recivied email with the menssage “no rows selected”. I think this example only should just send email when any blocking sessions it’s detected?
Hi again. I resolved this issue puting in the “if” condition -ge 2 instead -ge 1.
Thanks Samuel,
Another workaround is to add the set feed off in blocker.sql
I have updated the same in this KB.
Regards