Oracle 閒置連線自動斷開實作：DBMS_JOB 監控 V$SESSION 釋放資源

問題背景

兩層架構應用中，用戶操作完畢不斷線，長期佔用 Session、PGA、Locks，導致資料庫連線數耗盡、效能下降。

核心邏輯

• 查詢 V$SESSION 找出閒置超過門檻的 Session
• 關鍵欄位：LOGON_TIME (登入時間)、LAST_CALL_ET (最後呼叫經過秒數)
• 排除系統後台程序、特定機器/帳號 (批次作業、長跑報表)
• 以 ALTER SYSTEM KILL SESSION 'sid,serial#' 強制斷開

識別閒置 Session 查詢

SELECT s.sid, s.serial#, s.username, s.machine, s.osuser, s.logon_time,
       s.last_call_et AS idle_seconds,
       p.spid AS os_pid,
       'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''';' AS kill_cmd
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.type = 'USER'
  AND s.status = 'INACTIVE'
  AND s.last_call_et > 3600  -- 閒置超過 1 小時
  AND s.username NOT IN ('SYS','SYSTEM','DBSNMP')
  AND s.machine NOT IN ('batch-server-01','report-server-02'); -- 排除機器

儲存程序：自動偵測與殺除

CREATE OR REPLACE PROCEDURE kill_idle_sessions(p_idle_minutes IN NUMBER DEFAULT 60) AS
  v_killed NUMBER := 0;
BEGIN
  FOR r IN (
    SELECT sid, serial#
    FROM v$session
    WHERE type = 'USER'
      AND status = 'INACTIVE'
      AND last_call_et > p_idle_minutes * 60
      AND username NOT IN ('SYS','SYSTEM','DBSNMP')
      AND machine NOT IN ('batch-server-01','report-server-02')
  ) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || r.sid || ',' || r.serial# || '''';
      v_killed := v_killed + 1;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Kill failed: ' || r.sid || ' - ' || SQLERRM);
    END;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Completed. Killed ' || v_killed || ' sessions.');
END;
/

排程作業：DBMS_JOB 定時執行

-- 必須以 SYS 身分建立
VAR jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    job       => :jobno,
    what      => 'kill_idle_sessions(60);',
    next_date => SYSDATE,
    interval  => 'SYSDATE + 1/48'  -- 每 30 分鐘執行一次
  );
  COMMIT;
END;
/
PRINT jobno

10g+ 改用 DBMS_SCHEDULER (功能更強)

BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'JOB_KILL_IDLE_SESSIONS',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'KILL_IDLE_SESSIONS',
    number_of_arguments => 1,
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=30',
    enabled         => TRUE,
    comments        => 'Kill sessions idle > 60 min'
  );
  DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE('JOB_KILL_IDLE_SESSIONS', 1, 60);
END;
/

替代方案：Resource Manager (11g+ 推薦)

BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP('LOW_PRIORITY', 'Idle sessions');
  DBMS_RESOURCE_MANAGER.CREATE_PLAN('IDLE_KILL_PLAN', 'Kill idle sessions');
  DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(
    DBMS_RESOURCE_MANAGER.ORACLE_USER, 'APP_USER', 'LOW_PRIORITY');
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan => 'IDLE_KILL_PLAN',
    group_or_subplan => 'LOW_PRIORITY',
    comment => 'Auto switch to kill after idle',
    max_idle_time => 3600,  -- 秒
    switch_group => 'KILL_SESSION',
    switch_time => 0);
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

注意事項

• KILL SESSION 標記 Session 待終止，實際釋放需等待 Session 回應 (可能被長時間操作阻塞)
• 11g+ 可用 ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION 等待交易完成
• 三層架構 (Connection Pool) 應在中介層設定 Idle Timeout，資料庫層為最後防線
• 謹慎設定排除清單，避免誤殺批次/報表/維護作業