Oracle 殭屍鎖定會話徹底清除：從優雅 KILL 到 OS 強制終結

現象：KILLED 狀態卻久不釋放鎖

執行 ALTER SYSTEM KILL SESSION 'sid,serial#' 後，會話狀態變為 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

⚠️ **Windows 注意**：SPID 是**執行緒 ID**，不是 Task Manager 的 PID，必須用上述查詢取得。

進階：自動化清除腳本（謹慎部署）

CREATE OR REPLACE PROCEDURE kill_zombie_sessions(p_max_wait_sec IN NUMBER DEFAULT 300) AS
  v_spid VARCHAR2(24);
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
  ) LOOP
    -- 記錄審計
    INSERT INTO kill_audit_log (sid, serial, spid, kill_time) VALUES (r.SID, r.SERIAL#, r.SPID, SYSDATE);
    -- 嘗試優雅殺（雖狀態已 KILLED，再執行一次可觸發 PMON 加速）
    EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || r.SID || ',' || r.SERIAL# || ''' IMMEDIATE';
    -- 等待 10 秒
    DBMS_LOCK.SLEEP(10);
    -- 若仍存在，OS 強殺（需建立外部程式或 Scheduler Job 呼叫 OS 命令）
    -- 此處略過 OS 層面實作，建議由外部監控腳本處理
  END LOOP;
  COMMIT;
END;
/
-- 建立 Scheduler Job 每 5 分鐘執行一次
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'AUTO_KILL_ZOMBIE_SESSIONS',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'KILL_ZOMBIE_SESSIONS',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
    enabled         => TRUE
  );
END;
/

⚠️ 風險警告：自動殺會話屬高風險操作，**嚴禁無人值守直接 OS kill**。建議：

1. 僅記錄候選會話，發送告警給 DBA 人工確認。
2. 若必須自動化，限定條件：STATUS='KILLED' 且 LAST_CALL_ET > 300 且**非關鍵背景進程**（排除 PMON、SMON、DBWn、LGWR、CKPT、ARCH、RECO 等）。
3. 保留完整審計日誌，事後可追溯。

預防勝於治療：應用與架構層面

1. 設定 SQLNET.EXPIRE_TIME：SQLNET.ORA 加入 SQLNET.EXPIRE_TIME = 10，啟用死連接檢測，讓 Server 端主動清理斷開的 Client 會話。
2. Resource Manager 限制閒置時間：

   BEGIN
     DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
       plan             => 'DAYTIME_PLAN',
       group_or_subplan => 'OLTP_GROUP',
       comment          => 'Kill idle sessions after 30 min',
       max_idle_time    => 1800,          -- 秒
       max_idle_blocker_time => 600       -- 阻塞他人時更短
     );
   END;
   /
   ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'DAYTIME_PLAN';

3. 應用層連接池配置：啟用 validateConnection、idleTimeout、maxLifetime，避免連接池洩漏產生殭屍會話。
4. 長跑操作異步化：報表、批次改為 DBMS_SCHEDULER/外部排程，避免佔用連接池長時間持有鎖。

結語

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