您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

Oracle 8i/9i/10g/11g/12c/19c 空閒連線自動斷開實戰：v$session 監控 + DBMS_JOB 定時清理

編輯：MySQL 資料庫相關知識

問題背景

兩層架構應用，用戶操作完成後長時間不斷開，佔用會話資源
大型系統 (稅務/工商) 連線數有限，空閒會話導致資源耗盡、性能下降
三層架構可用連線池 (共享池) 緩解，但傳統 C/S 架構需資料庫層面強制清理

核心監控視圖：v$session

關鍵欄位	類型	含義
`LOGON_TIME`	DATE	用戶登入時間
`LAST_CALL_ET`	NUMBER	上次 SQL 執行結束後經過秒數 (每次執行歸零)
`SID, SERIAL#`	NUMBER	會話唯一標識，KILL 時需用
`STATUS`	VARCHAR2	ACTIVE / INACTIVE / KILLED
`MACHINE, OSUSER, PROCESS`	VARCHAR2	客戶端機器、OS 用戶、進程 ID
`MODULE, ACTION`	VARCHAR2	應用設定的模塊/動作 (DBMS_APPLICATION_INFO)

識別長時間空閒會話 SQL

SELECT 
  s.username,
  s.status,
  s.machine,
  s.osuser,
  p.spid,
  'kill -9 ' || p.spid AS unix_kill_cmd,
  'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' AS oracle_kill_cmd,
  TO_CHAR(s.logon_time, 'YYYY-MM-DD HH24:MI:SS') AS logon_time,
  s.last_call_et AS idle_seconds,
  FLOOR(s.last_call_et/3600) || 'h ' || FLOOR(MOD(s.last_call_et,3600)/60) || 'm' AS idle_hm,
  s.module
FROM v$session s
JOIN v$process p ON p.addr = s.paddr
WHERE s.type = 'USER'
  AND s.status != 'KILLED'
  -- 排除特定機器 (如批次處理伺服器)
  -- AND s.machine NOT IN ('batch-server-01','batch-server-02')
  AND s.last_call_et > 3600  -- 空閒超過 1 小時
ORDER BY s.last_call_et DESC;

自動清理存儲過程

CREATE OR REPLACE PROCEDURE p_monitor_idle_sessions (
  p_idle_minutes IN NUMBER DEFAULT 60
) AS
  v_kill_cmd VARCHAR2(200);
  CURSOR c_idle_sessions (p_min NUMBER) IS
    SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;' AS kill_cmd
    FROM v$session s
    JOIN v$process p ON p.addr = s.paddr
    WHERE s.type = 'USER'
      AND s.status != 'KILLED'
      -- AND s.machine NOT IN ('excluded-host')
      AND s.last_call_et > p_min * 60;
BEGIN
  FOR r IN c_idle_sessions(p_idle_minutes) LOOP
    v_kill_cmd := r.kill_cmd;
    EXECUTE IMMEDIATE v_kill_cmd;
    -- 可選：記錄審計日誌
    -- INSERT INTO audit_kill_sessions (kill_time, kill_cmd) VALUES (SYSDATE, v_kill_cmd);
  END LOOP;
END;
/
-- 必須以 SYS/SYSDBA 執行創建並授權
GRANT EXECUTE ON p_monitor_idle_sessions TO PUBLIC; -- 或特定 DBA 角色

DBMS_JOB 定時任務 (8i/9i/10g/11g 通用)

-- SYS 用戶建立任務：每 30 分鐘執行一次，清理空閒 > 60 分鐘
DECLARE
  v_jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    job       => v_jobno,
    what      => 'p_monitor_idle_sessions(60);',
    next_date => SYSDATE,
    interval  => 'SYSDATE + 30/1440'  -- 30 分鐘 = 30/1440 天
  );
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Job created: ' || v_jobno);
END;
/

-- 查看任務
SELECT job, what, next_date, interval, broken FROM dba_jobs WHERE what LIKE '%p_monitor%';

-- 移除任務
EXEC DBMS_JOB.REMOVE(<jobno>);

12c/19c 現代替代：DBMS_SCHEDULER

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

-- 查看執行歷史
SELECT log_date, job_name, status, error# FROM dba_scheduler_job_run_details 
WHERE job_name = 'JOB_KILL_IDLE_SESSIONS' ORDER BY log_date DESC;