Monitoring Blocking Sessions in Oracle (RAC & Single Instance)

ADVERTISEMENT

This script is part of the Shell Script Automations series for Oracle DBAs. It proactively checks for blocking sessions in the database and alerts the DBA team via email if any sessions are blocked for more than 10 seconds. It is RAC-aware and captures detailed session information (SQL ID, wait events, object details), helping DBAs quickly identify and act on database contention issues.

📜 Shell Script and SQL

✅ blocker.sql

Save this enhanced SQL file as /home/oracle/monitor/blocker.sql. It works for both RAC and single-instance setups and provides comprehensive session diagnostics.

-- blocker.sql : List blocking sessions with enhanced details
set feed off
set pagesize 200
set linesize 300
col event for a40
col username for a20
col program for a25
col sql_id for a15
col module for a20

SELECT
    s.inst_id,
    s.sid,
    s.serial#,
    s.sql_id,
    s.username,
    s.program,
    s.module,
    s.event,
    s.seconds_in_wait,
    s.blocking_session,
    s.blocking_session_status
FROM
    gv$session s
WHERE
    s.blocking_session IS NOT NULL
    AND s.seconds_in_wait > 10
ORDER BY
    s.seconds_in_wait DESC;

✅ blocker.sh

#!/bin/bash

################################################################################
# Title   : Blocking Session Monitoring Script for Oracle DB
# Script  : blocker.sh
# Purpose : Detects sessions blocked for more than 10 seconds and sends alerts.
#
# Features:
#   - RAC-aware via gv$session
#   - Captures SQL ID, program, module, and blocking session status
#   - Sends alert email with detailed log if blockers found
#   - Lightweight and cron-friendly
#
# Author  : W3Buddy
# Version : 1.0
################################################################################

# 👉 Update these as per your environment
export ORACLE_HOME=/oracle/app/oracle/product/12.1.0/dbhome_1
export ORACLE_SID=ORCL
export PATH=$ORACLE_HOME/bin:$PATH

# 📁 Log and SQL file paths
LOGFILE="/home/oracle/monitor/block_alert.log"
SQLFILE="/home/oracle/monitor/blocker.sql"

# Run SQL to fetch blocking sessions
sqlplus -s "/ as sysdba" > /dev/null <<EOF
spool $LOGFILE
@$SQLFILE
spool off
exit
EOF

# 📨 Send email if any blockers are found
if grep -q "^[[:space:]]*[[:digit:]]" "$LOGFILE"; then
    mailx -s "🚨 BLOCKING SESSION DETECTED (>10s)" info.w3buddy@gmail.com < "$LOGFILE"
fi

⚙️ Setup Instructions

# ----------------------------------------------
# 1. Create the SQL and Shell Script
# ----------------------------------------------
mkdir -p /home/oracle/monitor

vi /home/oracle/monitor/blocker.sql
# (Paste the SQL script above and save)

vi /home/oracle/monitor/blocker.sh
# (Paste the shell script above and save)

# ----------------------------------------------
# 2. Make the script executable
# ----------------------------------------------
chmod +x /home/oracle/monitor/blocker.sh

# ----------------------------------------------
# 3. Test the script manually
# ----------------------------------------------
/home/oracle/monitor/blocker.sh

# View the log:
cat /home/oracle/monitor/block_alert.log

# ----------------------------------------------
# 4. Schedule it with Cron (Every 1 minute)
# ----------------------------------------------
crontab -e

# Add the below line:
* * * * * /home/oracle/monitor/blocker.sh > /tmp/block.log 2>&1

# ----------------------------------------------
# 5. Confirm the cron entry
# ----------------------------------------------
crontab -l

ADVERTISEMENT