Oracle 殭屍會話強制清除實戰：從 SQL KILL 到 OS orakill/kill -9

現象：ALTER SYSTEM KILL SESSION 後狀態變 KILLED 但鎖不釋

常見原因：會話執行不可中斷操作（網路 I/O、外部程式、長跑 PL/SQL）、PMON 清理延遲、分散式交易 Prepared 狀態待協調。

四步驟標準清除流程（生產環境必背）

1. 定位：被鎖物件 + 持有會話

COL OBJECT_NAME FOR A30
COL MACHINE FOR A20
SELECT o.OBJECT_NAME, s.MACHINE, s.SID, s.SERIAL#
FROM V$LOCKED_OBJECT l
JOIN DBA_OBJECTS o ON o.OBJECT_ID = l.OBJECT_ID
JOIN V$SESSION s ON s.SID = l.SESSION_ID;

2. 優雅殺會話（首選）

ALTER SYSTEM KILL SESSION 'sid,serial#';

批量產生指令（**人工核對後逐條執行**，嚴禁管道批量）：

SELECT 'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# || ''';'
FROM V$LOCKED_OBJECT l
JOIN V$SESSION s ON s.SID = l.SESSION_ID;

3. 查詢 OS 進程/執行緒 ID (SPID)

SELECT p.SPID, s.OSUSER, s.PROGRAM
FROM V$SESSION s
JOIN V$PROCESS p ON p.ADDR = s.PADDR
WHERE s.SID = :target_sid;

4. OS 層面強制終結（終極手段）

範例：orakill ORCL 12345

進階：自動化清除腳本（謹慎部署，建議改為告警）

CREATE OR REPLACE PROCEDURE kill_zombie_sessions(p_max_wait_sec IN NUMBER DEFAULT 300) AS
BEGIN
  FOR r IN (
    SELECT s.SID, s.SERIAL#, p.SPID
    FROM V$SESSION s
    JOIN V$PROCESS p ON p.ADDR = s.PADDR
    WHERE s.STATUS = 'KILLED'
      AND s.LAST_CALL_ET > p_max_wait_sec
      AND s.TYPE != 'BACKGROUND'  -- 保護核心背景進程
  ) LOOP
    INSERT INTO kill_audit_log (sid, serial, spid, kill_time, action)
    VALUES (r.SID, r.SERIAL#, r.SPID, SYSDATE, 'AUTO_KILL_ATTEMPT');
    EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || r.SID || ',' || r.SERIAL# || ''' IMMEDIATE';
    DBMS_LOCK.SLEEP(10);
    -- 此處不直接 OS kill，建議外部監控腳本讀取 audit_log 執行 orakill/kill -9
  END LOOP;
  COMMIT;
END;
/

風險控制**：

• 僅記錄候選、發送告警，**人工確認後執行 OS kill**。
• 排除背景進程（PMON、SMON、DBWn、LGWR、CKPT、ARCH、RECO、MMON 等）。
• 保留完整審計日誌（SID、SPID、時間、操作人、前後狀態）。

預防機制：從源頭杜絕殭屍會話

1. SQLNET.EXPIRE_TIME（$ORACLE_HOME/network/admin/sqlnet.ora）：

   SQLNET.EXPIRE_TIME = 10  -- 分鐘，檢測死連接並主動清理

2. Resource Manager 閒置清理：

   BEGIN
     DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
       plan                  => 'DAY_PLAN',
       group_or_subplan      => 'APP_GROUP',
       max_idle_time         => 1800,        -- 30 分鐘閒置自動斷開
       max_idle_blocker_time => 600          -- 阻塞他人時 10 分鐘
     );
   END;
   /
   ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DAY_PLAN';

3. 應用層連接池驗證：validationQuery=SELECT 1 FROM DUAL、testOnBorrow=true、minEvictableIdleTimeMillis、maxWait。
4. 長跑操作異步化：報表/批次改用 DBMS_SCHEDULER、外部排程，避免佔用連接池長時間持有鎖。

避坑指南

結語

清除殭屍會話標準動作：**定位 → 優雅 KILL → 等待 PMON → OS 強殺 (orakill/kill -9)**。但**頻繁出現殭屍會話本身就是架構缺陷的症狀**。請優先落實 SQLNET.EXPIRE_TIME、Resource Manager 閒置清理、連接池驗證機制，從源頭杜絕「KILLED 卻不死」的會話。**DBA 的最高境界不是殺會話快，而是讓會話根本不需要被殺。**