Oracle数据库连接I管理-定期定理INACTIVE会话

原创已于 2026-03-23 17:16:46 修改 · 352 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#数据库 #oracle

于 2021-05-18 15:24:34 首次发布

ORACLE数据库监听listener相关同时被 2 个专栏收录

30 篇文章

订阅专栏

ORACLE数据库plsql开发

5 篇文章

订阅专栏

开发板推荐：天空星STM32F407VET6开发板

超高性价比 STM32主控 | 超高主频 | 一板兼容百芯 | 比赛神器 | 沉金彩色丝印

点击查看

在 Oracle RAC (Real Application Clusters) 环境中，控制并自动杀掉超过 20 分钟未活动（INACTIVE）的会话，主要有两种成熟方案：

推荐方案（原生自动化）：使用数据库参数 MAX_IDLE_TIME（Oracle 12.2 及以上版本支持）。这是最稳定、开销最小的方法，由数据库内核自动处理，无需额外脚本。
传统方案（脚本定时任务）：使用 PROFILE 的 IDLE_TIME 限制（需开启 RESOURCE_LIMIT）或编写定时脚本轮询 GV$SESSION 并执行 ALTER SYSTEM KILL SESSION。适用于旧版本或需要更复杂逻辑的场景。

以下是详细实施步骤和分析：

方案一：使用 `MAX_IDLE_TIME` 参数（推荐，Oracle 12.2+）

从 Oracle 12.2 开始，引入了 MAX_IDLE_TIME 参数，专门用于自动终止空闲会话。它比 Profile 更灵活，支持全局、PDB 级别设置，且不需要开启 RESOURCE_LIMIT。

1. 检查当前版本和参数

首先确认你的 RAC 数据库版本是否支持该参数（12.2, 18c, 19c, 21c 等均支持）。

-- 检查版本
SELECT * FROM v$version;

-- 检查当前 MAX_IDLE_TIME 设置 (单位：秒)
-- 注意：这是一个动态参数，可以在系统级别修改
SHOW PARAMETER max_idle_time;

2. 设置自动杀死 20 分钟空闲会话

20 分钟 = 1200 秒。你可以选择在 CDB 根容器（影响所有 PDB）或特定的 PDB 中设置。

全局设置（影响整个 CDB 及所有 PDB）：

-- 设置为 1200 秒 (20 分钟)
ALTER SYSTEM SET MAX_IDLE_TIME = 1200 SCOPE=BOTH SID='*';

SCOPE=BOTH: 立即生效并写入参数文件，重启后依然有效。
SID='*': 在 RAC 所有节点上同时生效。

针对特定 PDB 设置（如果使用了多租户架构）：

-- 切换到目标 PDB
ALTER SESSION SET CONTAINER = your_pdb_name;

-- 在该 PDB 级别设置
ALTER SYSTEM SET MAX_IDLE_TIME = 1200 SCOPE=BOTH;

3. 验证效果

当会话空闲时间超过 1200 秒后，Oracle 后台进程会自动终止该会话。

客户端会收到错误：ORA-03113: end-of-file on communication channel 或 ORA-02396: exceeded maximum idle time, please connect again。
可以通过查询 GV$SESSION 观察状态变化，或者直接观察长时间空闲的会话是否消失。

优点：

内核级处理：无需额外进程或脚本，性能开销极低。
RAC 天然支持：参数在所有节点同步，自动处理跨节点会话。
精准控制：按秒设置，灵活度高。

方案二：使用 PROFILE 的 `IDLE_TIME`（通用，所有版本）

如果你的版本低于 12.2，或者希望针对不同用户组设置不同的空闲时间，可以使用 Profile。

1. 开启资源限制

IDLE_TIME 生效的前提是数据库参数 RESOURCE_LIMIT 必须为 TRUE。

-- 检查状态
SHOW PARAMETER resource_limit;

-- 如果为 FALSE，则开启（动态生效，无需重启）
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE SCOPE=BOTH SID='*';

2. 创建或修改 Profile

IDLE_TIME 的单位是分钟。

-- 创建一个新的 Profile (例如叫 IDLE_20_MIN)
-- LIMIT IDLE_TIME 20 表示 20 分钟
CREATE PROFILE IDLE_20_MIN LIMIT IDLE_TIME 20;

-- 或者修改默认 Profile (影响所有未指定 Profile 的用户，需谨慎)
-- ALTER PROFILE DEFAULT LIMIT IDLE_TIME 20;

3. 将 Profile 分配给用户

-- 将新 Profile 赋予特定用户
ALTER USER your_username PROFILE IDLE_20_MIN;

4. 工作机制与注意事项

状态变化：当用户空闲超过 20 分钟，会话状态会从 INACTIVE 变为 SNIPED。
实际断开：SNIPED 状态的会话并不会立即从 GV$SESSION 中消失，也不会立即释放所有资源。只有当用户再次尝试操作该会话时，Oracle 才会真正切断连接并报错 ORA-02396。
缺点：如果只是单纯挂着不操作，会话可能一直显示为 SNIPED 占用连接数槽位。如果需要彻底清除 SNIPED 会话，通常还需要配合定时脚本清理。

方案三：定时脚本清理（最灵活，适用于复杂场景）

如果上述原生方法无法满足需求（例如需要记录日志、排除特定程序、或在旧版本中彻底清除 SNIPED 会话），可以编写 PL/SQL 脚本并通过 DBMS_SCHEDULER 定时运行。

1. 创建清理脚本

创建一个存储过程或脚本文件，逻辑如下：

查询 GV$SESSION（注意是全局视图 GV$ 而不是 V$，因为要查所有 RAC 节点）。
过滤条件：STATUS = 'INACTIVE' 且 LAST_CALL_ET > 1200 (20分钟 * 60秒)。
排除系统会话和当前会话。
执行 ALTER SYSTEM KILL SESSION 'sid,serial@inst_id' IMMEDIATE。

CREATE OR REPLACE PROCEDURE kill_inactive_sessions AS
    v_sql VARCHAR2(200);
    v_count NUMBER := 0;
BEGIN
    FOR rec IN (
        SELECT sid, serial#, inst_id, username, program
        FROM gv$session
        WHERE status = 'INACTIVE'
          AND last_call_et > 1200  -- 20 minutes * 60 seconds
          AND type != 'BACKGROUND' -- 排除后台进程
          AND username IS NOT NULL -- 排除系统会话
          AND program NOT LIKE '%(J00%)' -- 可选：排除调度器进程
          AND (sid, serial#, inst_id) != (SELECT sid, serial#, inst_id FROM v$mystat m, v$statname s WHERE m.statistic# = s.statistic# AND s.name = 'session id') -- 排除自己 (简化写法，实际需更严谨)
    ) LOOP
        BEGIN
            -- 构造杀会话语句，注意 RAC 需要 @inst_id
            v_sql := 'ALTER SYSTEM KILL SESSION ''' || rec.sid || ',' || rec.serial# || '@' || rec.inst_id || ''' IMMEDIATE';
            EXECUTE IMMEDIATE v_sql;
            v_count := v_count + 1;
            
            -- 可选：记录日志到表
            -- INSERT INTO kill_log (...) VALUES (...);
        EXCEPTION
            WHEN OTHERS THEN
                -- 记录错误，继续处理下一个
                NULL; 
        END;
    END LOOP;
    
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Killed ' || v_count || ' inactive sessions.');
END;
/

2. 创建定时任务

使用 DBMS_SCHEDULER 每 5 或 10 分钟运行一次该过程。

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'JOB_CLEAN_INACTIVE_SESSIONS',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN kill_inactive_sessions; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=MINUTELY; INTERVAL=5', -- 每 5 分钟运行一次
        enabled         => TRUE,
        comments        => 'Kill inactive sessions > 20 mins in RAC'
    );
END;
/

总结与建议

特性	方案一: MAX_IDLE_TIME	方案二: PROFILE IDLE_TIME	方案三: 定时脚本
适用版本	12.2 及以上	所有版本	所有版本
自动化程度	⭐⭐⭐⭐⭐ (内核自动)	⭐⭐⭐ (需再次触发才断开)	⭐⭐⭐⭐ (依赖调度器)
资源释放	立即彻底释放	标记为 SNIPED，再次操作才释放	立即强制释放
RAC 支持	完美支持 (参数同步)	支持	需用 `GV$SESSION` 和 `@inst_id`
灵活性	中 (按时间阈值)	中 (按用户组)	高 (可定制排除规则、日志等)
推荐指数	首选	次选 (旧版本)	特殊需求

最终建议：

如果是 Oracle 12.2 或更高版本，请直接使用 方案一 (MAX_IDLE_TIME)。这是最标准、最稳健的做法。
```
ALTER SYSTEM SET MAX_IDLE_TIME = 1200 SCOPE=BOTH SID='*';
```
如果是 旧版本 且能接受 SNIPED 状态暂时占用槽位，使用 方案二 (Profile)。
如果需要立即物理断开且不留任何 SNIPED 痕迹，或者需要复杂的白名单/黑名单逻辑，请使用 方案三 (定时脚本)。

特别提示：在执行杀会话操作前，请确保应用程序具备断线重连机制，避免因会话被杀导致业务中断报错。

2 脚本清理脚本如下：

CREATE OR REPLACE PROCEDURE SYS.DB_KILL_INACTIVE_CLIENTS AS
sql1 varachar2(1000);
BEGIN
FOR I IN
(SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
FROM gv$session S
WHERE S.USERNAME IS NOT NULL
AND UPPER(S.PROGRAM) IN ('xxx', 'xxx.EXE')
AND LOGONG_TIME<TRUNC(SYSDATE,'DD')-INTERVAL '4' HOUR
AND S.STATUS= 'INACTIVE'
ORDER BY INST_ID ASC
) LOOP
---------------------------------------------------------------------------
-- kill inactive sessions immediately
---------------------------------------------------------------------------
execute immediate 'alter system kill session ''' || rec.sid || ', ' ||
rec.serial# || '''immediate' ;
END LOOP;
END DB_KILL_INACTIVE_CLIENTS;

#!/bin/bash

logfile=/home/oracle/cron/session/log/killSession.log

echo " " >> $logfile 2>&1
echo "START ----`date`" >> $logfile 2>&1 ---此处记录开始时间日志
sqlplus /nolog <<STATS
connect / as sysdba
exec sys.db_kill_idle_clients;
exit;
STATS

echo "END ------`date`" >> $logfile 2>&1 --此处记录结束时间日志，查看日志查看是否执行即可。