Batch script to kill blocked session

An oracle database administrator often face a situation where a deadlock is observed in database. A deadlock occurs when two or more sessions are waiting for data locked by each other, resulting in all the sessions being blocked.

As a DBA, the only way you can resolve a lock by killing either the blocking session or blocked session.

To automate this process, I have prepared a batch script for windows which will kill the session after 10 mins of getting locked.

Step 1. Lets prepare the sql first which will find out the session that are locked more than 10mins

SET LINESIZE 1000 SET PAGESIZE 1000 SET FEEDBACK OFF   SELECT s.sid, s.serial#, ROUND((SYSDATE - s.logon_time) * 24 * 60) AS logon_time_minutes FROM v$session s, v$session_wait w WHERE s.sid = w.sid AND w.event = 'enq: TX - row lock contention' AND s.status = 'ACTIVE' AND (SYSDATE - s.logon_time) * 24 * 60 >= 10; exit;

Lets save the above sql as blocked_sessions_query.sql

Step 2. I will now prepare the batch script which will kill the session based on output retrieved from above sql

@echo on   REM Oracle database connection details   SET ORACLE_USER=sys SET ORACLE_PASSWORD=welcome123# SET ORACLE_SID=ORCL SET ORACLE_HOME=D:\oracle\product\19.3.0\dbhome_1   REM Define log file path SET LOG_FILE=F:\script_log.txt   REM SQL file containing the query to identify blocked sessions SET SQL_FILE=F:\blocked_sessions_query.sql   %ORACLE_HOME%\bin\sqlplus -S %ORACLE_USER%/%ORACLE_PASSWORD%@%ORACLE_SID% as sysdba @%SQL_FILE% > F: \session_id.txt     REM Execute SQL query using SQL*Plus and fetch results FOR /F "tokens=1,2,3" %%a IN (F:\session_id.txt) DO (     REM Set session details     set sid=%%a     set serial#=%%b     set logon_time_minutes=%%c )     REM If session has been active for at least 15 minutes, kill the session     if %logon_time_minutes% GEQ 15 (         echo Killing session %sid% (serial#: %serial#%)         echo ALTER SYSTEM KILL SESSION '%sid%,%serial#%' immediate; | %ORACLE_HOME%\bin\sqlplus -S %ORACLE_USER%/%ORACLE_PASSWORD%@%ORACLE_SID% as sysdba         REM Call email notification function here     )     exit /b

Save the above batch file as deadlock_kill.bat

Step 3. Now schedule the above script in task scheduler

Click on and provide password to set it.

Disclaimer:- This script should be tested properly in uat/test environment and it is not advised to run it directly on production database. The script automatically selects the session and kills it. However before killing the session, it is recommended you must send the information to application team to get their approval on which session to be killed.