{"id":4278,"date":"2025-06-16T23:58:20","date_gmt":"2025-06-16T23:58:20","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4278"},"modified":"2025-06-16T23:58:21","modified_gmt":"2025-06-16T23:58:21","slug":"monitoring-blocking-sessions-in-oracle-rac-single-instance","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/shell-scripts\/monitoring-blocking-sessions-in-oracle-rac-single-instance\/","title":{"rendered":"Monitoring Blocking Sessions in Oracle (RAC &amp; Single Instance)"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">This script is part of the <strong>Shell Script Automations<\/strong> 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.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">\ud83d\udcdc Shell Script and SQL<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">\u2705 blocker.sql<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">Save this enhanced SQL file as <code>\/home\/oracle\/monitor\/blocker.sql<\/code>. It works for both RAC and single-instance setups and provides comprehensive session diagnostics.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code><code>-- blocker.sql : List blocking sessions with enhanced details\nset feed off\nset pagesize 200\nset linesize 300\ncol event for a40\ncol username for a20\ncol program for a25\ncol sql_id for a15\ncol module for a20\n\nSELECT\n    s.inst_id,\n    s.sid,\n    s.serial#,\n    s.sql_id,\n    s.username,\n    s.program,\n    s.module,\n    s.event,\n    s.seconds_in_wait,\n    s.blocking_session,\n    s.blocking_session_status\nFROM\n    gv$session s\nWHERE\n    s.blocking_session IS NOT NULL\n    AND s.seconds_in_wait > 10\nORDER BY\n    s.seconds_in_wait DESC;<\/code><\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">\u2705 blocker.sh<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code><code>#!\/bin\/bash\n\n################################################################################\n# Title   : Blocking Session Monitoring Script for Oracle DB\n# Script  : blocker.sh\n# Purpose : Detects sessions blocked for more than 10 seconds and sends alerts.\n#\n# Features:\n#   - RAC-aware via gv$session\n#   - Captures SQL ID, program, module, and blocking session status\n#   - Sends alert email with detailed log if blockers found\n#   - Lightweight and cron-friendly\n#\n# Author  : W3Buddy\n# Version : 1.0\n################################################################################\n\n# \ud83d\udc49 Update these as per your environment\nexport ORACLE_HOME=\/oracle\/app\/oracle\/product\/12.1.0\/dbhome_1\nexport ORACLE_SID=ORCL\nexport PATH=$ORACLE_HOME\/bin:$PATH\n\n# \ud83d\udcc1 Log and SQL file paths\nLOGFILE=\"\/home\/oracle\/monitor\/block_alert.log\"\nSQLFILE=\"\/home\/oracle\/monitor\/blocker.sql\"\n\n# Run SQL to fetch blocking sessions\nsqlplus -s \"\/ as sysdba\" > \/dev\/null &lt;&lt;EOF\nspool $LOGFILE\n@$SQLFILE\nspool off\nexit\nEOF\n\n# \ud83d\udce8 Send email if any blockers are found\nif grep -q \"^&#91;&#91;:space:]]*&#91;&#91;:digit:]]\" \"$LOGFILE\"; then\n    mailx -s \"\ud83d\udea8 BLOCKING SESSION DETECTED (>10s)\" info.w3buddy@gmail.com &lt; \"$LOGFILE\"\nfi<\/code><\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">\u2699\ufe0f Setup Instructions<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code><code># ----------------------------------------------\n# 1. Create the SQL and Shell Script\n# ----------------------------------------------\nmkdir -p \/home\/oracle\/monitor\n\nvi \/home\/oracle\/monitor\/blocker.sql\n# (Paste the SQL script above and save)\n\nvi \/home\/oracle\/monitor\/blocker.sh\n# (Paste the shell script above and save)\n\n# ----------------------------------------------\n# 2. Make the script executable\n# ----------------------------------------------\nchmod +x \/home\/oracle\/monitor\/blocker.sh\n\n# ----------------------------------------------\n# 3. Test the script manually\n# ----------------------------------------------\n\/home\/oracle\/monitor\/blocker.sh\n\n# View the log:\ncat \/home\/oracle\/monitor\/block_alert.log\n\n# ----------------------------------------------\n# 4. Schedule it with Cron (Every 1 minute)\n# ----------------------------------------------\ncrontab -e\n\n# Add the below line:\n* * * * * \/home\/oracle\/monitor\/blocker.sh > \/tmp\/block.log 2>&amp;1\n\n# ----------------------------------------------\n# 5. Confirm the cron entry\n# ----------------------------------------------\ncrontab -l<\/code><\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,990],"class_list":["post-4278","cposts","type-cposts","status-publish","hentry","category-notes","category-shell-scripts"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4278","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4278"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4278"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}