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.
Leave a Reply