兩層 C/S 架構應用中,用戶執行操作後長時間不斷開連接 (如去開會、下班),佔用有限連接數、消耗 PGA/UGA 記憶體、增加服務器負載。三層 B/S 架構可用連接池緩解,但傳統 Client/Server 必須資料庫端主動清理。
| 欄位 | 類型 | 含義 | 用途 |
|---|---|---|---|
| SID | NUMBER | 會話標識符 | 殺會話主鍵 |
| SERIAL# | NUMBER | 會話序列號 (防誤殺) | ALTER SYSTEM KILL SESSION 'sid,serial#' |
| USERNAME | VARCHAR2 | 資料庫用戶名 | 識別業務用戶 |
| STATUS | VARCHAR2 | ACTIVE/INACTIVE/KILLED/SNIPED | 當前狀態 |
| MACHINE | VARCHAR2 | 客戶端機器名 | 識別來源主機 |
| OSUSER | VARCHAR2 | 作業系統用戶 | 關聯 OS 進程 |
| PROCESS | VARCHAR2 | 客戶端進程 ID | OS 層 kill -9 |
| SPID | VARCHAR2 | 服務器端專用/共享服務器進程 ID | OS 層 kill -9 (慎用) |
| LOGON_TIME | DATE | 登入時間 | 計算總連接時長 |
| LAST_CALL_ET | NUMBER | 最後一次調用結束後經過秒數 (空閒時間) | **核心判斷指標** |
| MODULE/ACTION | VARCHAR2 | DBMS_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;
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;
#!/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
-- 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)`
基於 `V$SESSION.LAST_CALL_ET` 識別長空閒會話,結合 `DBMS_JOB`/`DBMS_SCHEDULER` 定時掃描與 `ALTER SYSTEM KILL SESSION IMMEDIATE` 清理,輔以白名單、審計日誌、Resource Manager 多層防護,可有效回收被佔用的資料庫連接資源,保障核心業務可用性。