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


Related Topics

Shell script for monitoring Alert log
Shell script to delete old archives using RMAN
Shell script to monitor lag in standby datbase using dgmgrl
Shell script to monitor asm diskgroup usage
shell script for file system alert
Alert log rotation script in oracle
Shell script for monitoring blocking sessions
Tablespace monitoring shell script
Shell script to report failed login attempt in oracle
Shell script to monitor goldengate process

You May Also Like

Steps for upgrading oracle database to 19c using DBUA
Find user commits per minute in oracle database
Shell script for monitoring Alert log
How to enable active dataguard in physical standby database
How to recreate physical standby controlfile
Shell script to monitor asm diskgroup usage
Blackout targets in OEM 12c cloud control
Query clause in oracle datapump expdp
how to send mail using utl_mail in oracle 11g
Steps for changing public hostname for a standalone grid infrastructure

From This Website

Standby redologs in oracle dataguard
How to find cpu and memory information of oracle database server
Script to get cpu usage and wait event information in oracle database
How to deinstall/cleanup standalone grid infrastructure
Apply database patch from OEM Cloud control
CONTENT parameter in datapump
Steps for changing public hostname for a standalone grid infrastructure
Perform Flashback in pluggable database(PDB) in oracle 12.2
How to disable enable log shipping in standby using dgmgrl
shell script for file system alert