Oracle 死鎖/鎖定會話快速定位與終結指令大全

一、查詢被鎖物件與會話

-- 物件級
SELECT b.owner, b.object_name, a.session_id, a.locked_mode
FROM v$locked_object a, dba_objects b
WHERE b.object_id = a.object_id;

-- 會話級（含登入時間）
SELECT b.username, b.sid, b.serial#, b.logon_time
FROM v$locked_object a, v$session b
WHERE a.session_id = b.sid
ORDER BY b.logon_time;

二、標準 Kill Session 語法

ALTER SYSTEM KILL SESSION 'sid,serial#';
-- 例
ALTER SYSTEM KILL SESSION '29,5497';

-- 遇到 ORA-00031（會話標記為 kill 但未立即釋放）加 IMMEDIATE
ALTER SYSTEM KILL SESSION '29,5497' IMMEDIATE;

三、進階：鎖定存儲過程/包

-- 1. 查被鎖物件
SELECT * FROM v$db_object_cache WHERE owner='SCHEMA' AND locks != '0';

-- 2. 查持有會話
SELECT * FROM v$access WHERE owner='SCHEMA' AND name='OBJECT_NAME';

-- 3. 取 SID/SERIAL#/PADDR
SELECT sid, serial#, paddr FROM v$session WHERE sid = :sid;

-- 4. 取 OS 進程 SPID
SELECT spid FROM v$process WHERE addr = :paddr;

-- 5. 先 Kill Oracle Session，再 Kill OS Process
ALTER SYSTEM KILL SESSION 'sid,serial#';
-- Unix: kill -9 spid
-- Windows: orakill sid spid

四、死鎖專用查詢（含 SQL 文本）

SELECT t2.username || ' ' || t2.sid || ' ' || t2.serial# || ' ' || t2.logon_time || ' ' || t3.sql_text
FROM v$locked_object t1, v$session t2, v$sqltext t3
WHERE t1.session_id = t2.sid
  AND t2.sql_address = t3.address
ORDER BY t2.logon_time;

五、OS 層面強制終結（最後手段）

-- 取 OS Process ID
SELECT a.username, c.spid AS os_pid, c.pid AS ora_pid
FROM v$session a, v$process c
WHERE c.addr = a.paddr AND a.sid = :sid AND a.serial# = :serial;

-- Unix
ps -ef | grep os_pid
kill -9 os_pid

-- Windows (需 orakill.exe)
orakill sid spid

最佳實踐清單

1. 優先 KILL SESSION IMMEDIATE，給予交易回滾時間。
2. 若應用連線池未感知斷開，配合 SQLNET.EXPIRE_TIME 或防火牆半開連線清理。
3. 死鎖根因多為應用邏輯（更新順序不一致），殺會話治標，修碼治本。
4. 建立監控腳本：V$SESSION_WAIT + V$LOCK 告警長時間阻塞。