兩層架構 (Client/Server) 應用中，用戶操作完成後長時間不斷開，佔用有限的資料庫連線 (Process/Session)、PGA 記憶體、鎖資源，導致真實業務無法獲取連線、伺服器負載飆升。三層架構可用連線池 解決，兩層架構需資料庫端主動清理。
| 欄位 | 型別 | 含義 |
|---|---|---|
| SID / SERIAL# | NUMBER | Session 唯一識別，Kill 所需 |
| USERNAME | VARCHAR2 | 資料庫用戶 |
| STATUS | VARCHAR2 | ACTIVE / INACTIVE / KILLED |
| LOGON_TIME | DATE | 登入時間 |
| LAST_CALL_ET | NUMBER | 最後一次 Call 結束後經過秒數 (閒置時間核心指標) |
| MACHINE | VARCHAR2 | 客戶端主機名 |
| PROGRAM | VARCHAR2 | 客戶端程式名 |
| PADDR | RAW | 對應背景進程位址 (可關聯 V$PROCESS.SPID 取得 OS PID) |
SELECT s.SID, s.SERIAL#, s.USERNAME, s.STATUS, s.MACHINE, s.PROGRAM,
s.LOGON_TIME,
s.LAST_CALL_ET AS IDLE_SECONDS,
p.SPID AS OS_PID,
'ALTER SYSTEM KILL SESSION ''' || s.SID || ',' || s.SERIAL# || ''' IMMEDIATE;' 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','OUTLN') -- 排除系統帳號
AND s.MACHINE NOT IN ('batch-server','etl-server') -- 排除長跑批次機器
ORDER BY s.LAST_CALL_ET DESC;
CREATE OR REPLACE PROCEDURE pr_kill_idle_sessions (p_idle_minutes IN NUMBER DEFAULT 60) AS
v_kill_cmd VARCHAR2(200);
v_cnt PLS_INTEGER := 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','OUTLN')
AND MACHINE NOT IN ('batch-server','etl-server')
) LOOP
v_kill_cmd := 'ALTER SYSTEM KILL SESSION ''' || r.SID || ',' || r.SERIAL# || ''' IMMEDIATE';
BEGIN
EXECUTE IMMEDIATE v_kill_cmd;
v_cnt := v_cnt + 1;
DBMS_OUTPUT.PUT_LINE('Killed: ' || v_kill_cmd);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Failed to kill ' || r.SID || ': ' || SQLERRM);
END;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total killed: ' || v_cnt);
END;
/
CONNECT sys/password AS SYSDBA
VARIABLE jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => :jobno,
what => 'BEGIN pr_kill_idle_sessions(60); END;',
next_date => SYSDATE,
interval => 'SYSDATE + 30/1440' -- 每 30 分鐘執行一次
);
COMMIT;
END;
/
PRINT jobno;
若 Session 被標記 `KILLED` 但長時間未消失 (等待資源/rollback)，可在 OS 層面殺掉對應 Process：
# 從上述查詢取得 OS_PID (SPID)
kill -9
警告：OS Kill 為最後手段，可能導致資料檔損壞、Instance Crash，僅在 `ALTER SYSTEM KILL SESSION` 無效且確認該 Session 無關鍵交易時使用。
透過 `V$SESSION.LAST_CALL_ET` 識別閒置 Session、`DBMS_JOB` 定時執行 `ALTER SYSTEM KILL SESSION IMMEDIATE`、配合 `SQLNET.EXPIRE_TIME` 網路層探測，可在 Oracle 8i/9i 兩層架構環境有效回收閒置連線資源。現代版本 (11g+) 推薦使用 **Resource Manager** (`IDLE_TIME` 指令) 或 **Database Resident Connection Pooling (DRCP)** 統一管理，無需自訂 Job。
