11g: Oracle

Here’s a useful, practical post focused on a common DBA/developer pain point: identifying and killing hanging or blocking sessions . Oracle 11g: How to Find and Kill Blocking Sessions Blocked sessions can bring your application to a halt. Use this quick script to identify the culprit and resolve it. 1. Find the blocking session SELECT s1.username AS blocking_user, s1.sid AS blocking_sid, s1.serial# AS blocking_serial, s2.username AS waiting_user, s2.sid AS waiting_sid, s2.wait_class, s2.seconds_in_wait FROM v$lock l1, v$session s1, v$lock l2, v$session s2 WHERE l1.id1 = l2.id1 AND l1.id2 = l2.id2 AND l1.block = 1 AND l2.block = 0 AND l1.sid = s1.sid AND l2.sid = s2.sid; 2. Kill the blocking session Once you have BLOCKING_SID and BLOCKING_SERIAL :

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE; If you need to find the actual SQL causing the block: oracle 11g

ALTER SYSTEM KILL SESSION '142,3892' IMMEDIATE; Sometimes the killed session lingers. Use this to force cleanup: Here’s a useful, practical post focused on a

SELECT sql_id, sql_text FROM v$sql WHERE sql_id = (SELECT sql_id FROM v$session WHERE sid = <blocking_sid>); This will help you identify poorly written transactions or missing COMMIT statements. Use this to force cleanup: SELECT sql_id, sql_text