Oracle 鎖衝突 Session 查詢與 Kill 實戰腳本

一鍵查詢腳本

SELECT 
    dob.object_name          AS table_name,
    lo.session_id || ',' || vss.serial# AS kill_param,
    lo.locked_mode,
    lo.session_id,
    vss.serial#,
    vss.action               AS action,
    vss.osuser               AS osuser,
    vss.logon_time,
    vss.process              AS app_pid,
    vps.spid                 AS db_pid
FROM v$locked_object lo
JOIN dba_objects dob ON lo.object_id = dob.object_id
JOIN v$session vss ON lo.session_id = vss.sid
JOIN v$process vps ON vss.paddr = vps.addr
ORDER BY 2, 3, dob.object_name;

輸出欄位說明

• KILL_PARAM：直接用於 ALTER SYSTEM KILL SESSION '...'。
• LOCKED_MODE：6 = 排他鎖 (DML)，3 = 行排他 (SELECT FOR UPDATE)。
• APP_PID / DB_PID：應用進程 ID / 資料庫影子進程 ID，便於 OS 層面排查。

執行 Kill

BEGIN
    FOR r IN (
        SELECT sid, serial#
        FROM v$session
        WHERE blocking_session IS NOT NULL
          AND seconds_in_wait > 300  -- 等待超過 5 分鐘
          AND status = 'ACTIVE'
    ) LOOP
        EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || r.sid || ',' || r.serial# || ''' IMMEDIATE';
        DBMS_OUTPUT.PUT_LINE('Killed: ' || r.sid || ',' || r.serial#);
    END LOOP;
END;
/
-- 建議配合 DBMS_SCHEDULER 定期執行，並記錄審計日志

最佳實踐

1. 優先人工確認再 Kill，避免誤傷關鍵交易。
2. 生產庫建立 SELECT ANY DICTIONARY 角色專供監控帳號使用，避免 DBA 權限過大。
3. 結合應用層 SELECT FOR UPDATE NOWAIT / SKIP LOCKED (11g+) 減少業務層面死鎖。