萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 8i/9i 空閒連接自動斷開實作:V$SESSION 監控與後台任務殺死長佔用會話

問題場景

兩層 C/S 架構應用中,用戶執行操作後長時間不斷開連接 (如去開會、下班),佔用有限連接數、消耗 PGA/UGA 記憶體、增加服務器負載。三層 B/S 架構可用連接池緩解,但傳統 Client/Server 必須資料庫端主動清理。

核心監控視圖:V$SESSION 關鍵欄位

欄位類型含義用途
SIDNUMBER會話標識符殺會話主鍵
SERIAL#NUMBER會話序列號 (防誤殺)ALTER SYSTEM KILL SESSION 'sid,serial#'
USERNAMEVARCHAR2資料庫用戶名識別業務用戶
STATUSVARCHAR2ACTIVE/INACTIVE/KILLED/SNIPED當前狀態
MACHINEVARCHAR2客戶端機器名識別來源主機
OSUSERVARCHAR2作業系統用戶關聯 OS 進程
PROCESSVARCHAR2客戶端進程 IDOS 層 kill -9
SPIDVARCHAR2服務器端專用/共享服務器進程 IDOS 層 kill -9 (慎用)
LOGON_TIMEDATE登入時間計算總連接時長
LAST_CALL_ETNUMBER最後一次調用結束後經過秒數 (空閒時間)**核心判斷指標**
MODULE/ACTIONVARCHAR2DBMS_APPLICATION_INFO 設定的模組/動作識別業務功能模塊

識別長空閒會話核心查詢

SELECT 
  s.username         AS "用戶名",
  s.status           AS "狀態",
  s.machine          AS "客戶端機器",
  s.osuser           AS "OS用戶",
  p.spid             AS "服務器進程ID",
  'kill -9 ' || p.spid AS "OS級殺進程命令",
  'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''';' AS "Oracle級殺會話命令",
  TO_CHAR(s.logon_time, 'YYYY-MM-DD HH24:MI:SS') AS "登入時間",
  s.last_call_et     AS "空閒秒數",
  TRUNC(s.last_call_et/3600) || 'h ' || 
  TRUNC(MOD(s.last_call_et,3600)/60) || 'm' AS "空閒時長",
  s.module           AS "模塊"
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 ('BATCH_SERVER_01', 'ETL_SERVER_02')
  -- 空閒超過 1 小時 (3600 秒)
  AND s.last_call_et > 3600
ORDER BY s.last_call_et DESC;

自動化清理解決方案

方案一:DBMS_JOB 定時掃描 (8i/9i 相容)

CREATE OR REPLACE PROCEDURE kill_idle_sessions AS
  v_sql VARCHAR2(500);
  v_cnt NUMBER := 0;
BEGIN
  FOR rec IN (
    SELECT sid, serial#, machine, last_call_et
    FROM v$session
    WHERE type = 'USER'
      AND status = 'INACTIVE'
      AND last_call_et > 3600  -- 1 小時
      AND machine NOT IN (SELECT machine FROM excluded_machines) -- 白名單表
  ) LOOP
    v_sql := 'ALTER SYSTEM KILL SESSION ''' || rec.sid || ',' || rec.serial# || ''' IMMEDIATE';
    BEGIN
      EXECUTE IMMEDIATE v_sql;
      v_cnt := v_cnt + 1;
      -- 記錄審計日誌
      INSERT INTO session_kill_log (sid, serial#, machine, idle_sec, kill_time, sql_text)
      VALUES (rec.sid, rec.serial#, rec.machine, rec.last_call_et, SYSDATE, v_sql);
    EXCEPTION
      WHEN OTHERS THEN
        INSERT INTO session_kill_log (sid, serial#, machine, idle_sec, kill_time, error_msg)
        VALUES (rec.sid, rec.serial#, rec.machine, rec.last_call_et, SYSDATE, SQLERRM);
    END;
  END LOOP;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Killed ' || v_cnt || ' idle sessions.');
END;
/

-- 提交每小時執行一次
DECLARE
  v_job NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(v_job, 'kill_idle_sessions;', SYSDATE, 'SYSDATE + 1/24');
  COMMIT;
END;

方案二:SQL*Plus 腳本 + Cron (OS 層)

#!/bin/ksh
# kill_idle.sh - 每小時由 cron 執行
ORACLE_SID=PROD; export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/9.2.0; export ORACLE_HOME

sqlplus -s / as sysdba < 3600
  AND machine NOT IN ('BATCH_SERVER_01','ETL_SERVER_02');
SPOOL OFF
@/tmp/kill_cmds.sql
EXIT
EOF

方案三:Oracle 10g+ Resource Manager 自動管理 (推薦)

-- 1. 建立消費者群組
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(
    consumer_group => 'IDLE_USERS',
    comment        => '長空閒用戶自動降級/殺掉'
  );
END;
/

-- 2. 映射規則:空閒超過 30 分鐘自動切換至此群組
BEGIN
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    attribute      => 'IDLE_TIME',
    value          => '1800', -- 秒
    consumer_group => 'IDLE_USERS'
  );
END;
/

-- 3. 計劃指令:限制 IDLE_USERS 群組資源,甚至 KILL SESSION
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PLAN(plan => 'IDLE_KILL_PLAN', comment => '殺死長空閒會話');
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan             => 'IDLE_KILL_PLAN',
    group_or_subplan => 'IDLE_USERS',
    comment          => '強制斷開',
    max_idle_time    => 3600, -- 1 小時後強制斷開 (10gR2+)
    switch_group     => 'CANCEL_SQL', -- 或 KILL_SESSION
    switch_time      => 0
  );
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA;
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA;
END;
/

白名單機制設計

CREATE TABLE excluded_machines (
  machine_pattern VARCHAR2(100) PRIMARY KEY, -- 支援通配符 LIKE
  description     VARCHAR2(200),
  created_by      VARCHAR2(30) DEFAULT USER,
  created_date    DATE DEFAULT SYSDATE
);

INSERT INTO excluded_machines VALUES ('BATCH_%', '夜間批次處理服務器');
INSERT INTO excluded_machines VALUES ('REPORT_%', '報表服務器');
INSERT INTO excluded_machines VALUES ('%APP_SERVER%', '應用服務器 (連接池管理)');

查詢時改為:`AND NOT EXISTS (SELECT 1 FROM excluded_machines em WHERE s.machine LIKE em.machine_pattern)`

風險控制與最佳實踐

  • IMMEDIATE 關鍵字:`KILL SESSION ... IMMEDIATE` 立即回滾並釋放鎖,避免等待事務提交;
  • 審計日誌:每次殺會話必須記錄 (SID, SERIAL#, 機器, 用戶, 空閒時間, 執行 SQL, 操作人/自動任務);
  • 通知機制:殺掉關鍵應用會話前發送郵件/短信給應用擁有者確認;
  • 漸進式策略:先警告 (發送郵件/彈窗) → 降級資源 (Resource Manager) → 強制斷開;
  • 測試驗證:在測試環境模擬長空閒會話,驗證殺會話不導致應用崩潰 (連接池自動重連)。

總結

基於 `V$SESSION.LAST_CALL_ET` 識別長空閒會話,結合 `DBMS_JOB`/`DBMS_SCHEDULER` 定時掃描與 `ALTER SYSTEM KILL SESSION IMMEDIATE` 清理,輔以白名單、審計日誌、Resource Manager 多層防護,可有效回收被佔用的資料庫連接資源,保障核心業務可用性。

copyright © 萬盛學電腦網 all rights reserved