Oracle 8i 實現空閒連接自動斷開與重連監控方案

問題背景

兩層 C/S 架構下，用戶操作完畢長時間不斷開佔用連接，導致服務器資源耗盡、性能下降。三層 B/S 可用連接池解決，但傳統架構需後台任務強制清理。

核心邏輯：識別超時空閒會話

利用動態視圖 V$SESSION 兩個關鍵列：

• LOGON_TIME (DATE)：登入時間
• LAST_CALL_ET (NUMBER, 秒)：上次調用結束後經過時間，每次新調用歸零

識別查詢 SQL (含屏蔽機器)

SELECT S.USERNAME, S.STATUS, S.MACHINE, S.OSUSER, S.PROCESS,
       'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' || S.SERIAL# || '''' KILL_CMD,
       S.LOGON_TIME, S.LAST_CALL_ET/60 IDLE_MIN
FROM V$SESSION S, V$PROCESS P
WHERE S.TYPE = 'USER'
  AND P.ADDR = S.PADDR
  AND S.STATUS != 'KILLED'
  -- 排除特殊機器 (跑長報表等)
  AND SUBSTR(S.MACHINE,1,19) NOT IN ('REPORT-SERVER-01','BATCH-SERVER-02')
  AND S.LAST_CALL_ET > :IDLE_THRESHOLD_MIN * 60
ORDER BY S.LAST_CALL_ET DESC;

存儲過程：自動 KILL 超時會話

CREATE OR REPLACE PROCEDURE P_MONITOR (AN_MINUTES NUMBER DEFAULT 60) AS
  V_STR VARCHAR2(200);
  CURSOR C_USERS (V_MINUTES NUMBER) IS
    SELECT 'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' || S.SERIAL# || '''' KILL_CMD
    FROM V$SESSION S, V$PROCESS P
    WHERE S.TYPE = 'USER'
      AND P.ADDR = S.PADDR
      AND S.STATUS != 'KILLED'
      -- AND SUBSTR(S.MACHINE,1,19) NOT IN ('排除機器')
      AND S.LAST_CALL_ET > V_MINUTES * 60
    ORDER BY S.LAST_CALL_ET DESC;
BEGIN
  FOR R IN C_USERS(AN_MINUTES) LOOP
    V_STR := R.KILL_CMD;
    EXECUTE IMMEDIATE V_STR;
  END LOOP;
END;
/
-- 必須以 SYS 身份編譯並授權執行

DBMS_JOB 定時調度 (每 30 分鐘檢查一次，閾值 60 分鐘)

DECLARE
  JOBNO NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    JOB       => JOBNO,
    WHAT      => 'P_MONITOR(60);',
    NEXT_DATE => SYSDATE,
    INTERVAL  => 'SYSDATE + 30/1440'  -- 30 分鐘 = 30/1440 天
  );
  COMMIT;
END;
/

-- 驗證
SELECT JOB, WHAT, NEXT_DATE, INTERVAL FROM USER_JOBS;

關鍵注意點

1. 權限：存儲過程與 JOB 均需在 SYS 下創建/提交，否則 ALTER SYSTEM KILL SESSION 權限不足
2. KILL SESSION 原理：標記會話為 KILLED，PMON 進程異步清理資源，非即時斷開 TCP
3. 長事務保護：若會話正在執行長 SQL，LAST_CALL_ET 會歸零，不會被誤殺
4. 排除名單：批量報表、ETL 服務器務必加入排除條件
5. 現代替代方案：Oracle 11g+ SQLNET.ORA 設置 SQLNET.EXPIRE_TIME = 10 (分鐘) 啟用死連接檢測；資源管理計劃 IDLE_TIME 參數

擴展：客戶端主動重連模式

應用層捕獲 ORA-03113/03114 (端到端通信中斷) 異常 → 等待指數退避 → 重新獲取連接池連接 → 重試事務。配合服務端清理形成雙重保障。