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

You May Also Like

resumable_timeout parameter in oracle database
Shell script to monitor lag in standby datbase using dgmgrl
How to keep or delete columns/Variable of a data frame in R
ORA-32773: operation not supported for smallfile tablespace
Shell script to monitor asm diskgroup usage
DEFERRED_SEGMENT_CREATION parameter in oracle
How to get the execution plan for a SQL between two AWR snapshots
how to Print execution time of tasks in ansible
SETUP ORACLE 18C DATABASE - STEP BY STEP
ORA-16855: transport lag has exceeded specified threshold

From This Website

Clone a database using dbca command in oracle 19c -New feature
How to use oratop tool for oracle database monitoring
TNS-12542: TNS:address already in use
WHAT IS SQL PROFILE IN ORACLE
Purge AUD$ table using DBMS_AUDIT_MGMT
DBMS_PARALLEL_EXECUTE in oracle PL/SQL
Apply patch on oracle 12.2 database ( Release update)
Shell script to monitor goldengate process
ORA-30034: Undo tablespace cannot be specified as temporary tablespace
Script to get cpu usage and wait event information in oracle database