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@eraazi.com < $logfile
fi

3. configure in crontab( every one minute)

* * * * * /home/oracle/monitor/blocker.sh > /tmp/block.log
blocking SHELL SCRIPT


Related Topics

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

You May Also Like

Open wallet automatically after starting the database
How to generate AWR report in RAC
Shell script to delete old archives using RMAN
ORA-16855: transport lag has exceeded specified threshold
How to enable active dataguard in physical standby database
Oswatcher tool for collecting server diagnostic information
How to use oratop tool for oracle database monitoring
resumable_timeout parameter in oracle database
ORA-32773: operation not supported for smallfile tablespace
Apply database patch from OEM Cloud control

From This Website

LREG Background Process in oracle
ORA-02304: invalid object identifier literal while import with CREATE TYPE OID
Apply database patch from OEM Cloud control
Defgen utility in Oracle goldengate
Open wallet automatically after starting the database
Generate custom report from OEM cloud control
Upgrade database using OEM 12C cloud control
Lock account automatically with INACTIVE_ACCOUNT_TIME
ORA-32774: more than one file was specified for bigfile tablespace
Query clause in oracle datapump expdp