Oracle 鎖查詢與殺會話完整腳本

核心鎖查詢 SQL

SELECT SUBSTR(v$lock.sid, 1, 4) AS "SID",
       SUBSTR(username, 1, 12) AS "UserName",
       SUBSTR(object_name, 1, 25) AS "ObjectName",
       v$lock.type AS "LockType",
       DECODE(RTRIM(SUBSTR(lmode, 1, 4)),
              '2', 'Row-S (SS)',
              '3', 'Row-X (SX)',
              '4', 'Share',
              '5', 'S/Row-X (SSX)',
              '6', 'Exclusive',
              'Other') AS "LockMode",
       SUBSTR(v$session.program, 1, 25) AS "ProgramName"
FROM v$lock, sys.dba_objects, v$session
WHERE object_id = v$lock.id1
  AND v$lock.sid = v$session.sid
  AND username IS NOT NULL
  AND username NOT IN ('SYS', 'SYSTEM')
  AND serial# != 1;

鎖模式對照表

關聯會話詳細資訊

SELECT a.sid, a.serial#, b.spid, a.status, a.program
FROM v$session a, v$process b
WHERE a.paddr = b.addr
ORDER BY a.sid;

• `SPID`：作業系統進程 ID，可用於 OS 層面 `kill -9` (最後手段)
• `PROGRAM`：客戶端程式名稱，便於定位來源應用

殺掉阻塞會話

ALTER SYSTEM KILL SESSION '200,68' IMMEDIATE;
-- 格式：'SID,SERIAL#'
-- IMMEDIATE：立即終止，回滾交易，釋放鎖

進階：自動偵測並殺掉長時間阻塞

DECLARE
  CURSOR c_block IS
    SELECT s.sid, s.serial#, s.seconds_in_wait, s.sql_id
    FROM v$session s
    WHERE s.blocking_session IS NOT NULL
      AND s.seconds_in_wait > 300  -- 超過 5 分鐘
      AND s.status = 'ACTIVE';
BEGIN
  FOR r IN c_block LOOP
    EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || r.sid || ',' || r.serial# || ''' IMMEDIATE';
    DBMS_OUTPUT.PUT_LINE('Killed: ' || r.sid || ',' || r.serial# || ' SQL_ID: ' || r.sql_id);
  END LOOP;
END;
/
-- 可封裝為 DBMS_SCHEDULER 定時作業每分鐘執行

注意事項

• 殺會話會觸發交易回滾，長交易回滾可能耗時更久 (快速啟動回滾)。
• 優先嘗試聯繫應用擁有者正常提交/回滾。
• 若為 `TM` 鎖 (DML 鎖) 由無索引外鍵引起，根治是補建外鍵索引。