Oracle定期请理INACTIVE会话
导语:生产系统在大升级之后(11g到19c)出现了一个问题,大量空闲会话不自己释放,会慢慢的到达session限制的值,导致数据库问题。
经过观察,这些空闲的会话都出自一个用户:AOX_USER。与应用系统的人联系,他们也查不出什么原因导致的。所以只能在数据库端处理。也就是定时对空闲的会话进行请理。
要求:
- 请理INACTIVE状态,并超过3小时的会话。清理语句参考:oracle killed session
- 将请理的会话信息收集到一个表中,作为凭据或者日志。
方法
清理的方法有两种:
- 用resource_limit的idle_time限制,在profile中设置。参考:用户管理之profile
Oracle的idle限制机制优点是设置10分钟,空闲会话绝对不会活过11分钟。缺点是kill的会话信息不会存储。只能在alert日志中找到sid和serial number的信息。 - 用手动的alter system kill session语句,然后写存储过程定时执行。
优点是手动的kill可以将准确的信息同时收集起来。缺点是因为是定时任务不会像idle限制一样那么的即时。
最后,领导觉得用第一种方法更好,只不过收集的会话信息可能会有重复的。
实现第一种方法
- 修改idletime参数
首先查找
SYS@CSP> select username,profile from dba_users where username = 'AOX_USER';
USERNAME PROFILE
-------------
AOX_USER AOXUSER_IDLE
SYS@CSP> select * from dba_profiles where PROFILE = 'AOXUSER_IDLE' and RESOURCE_NAME= 'IDLE_TIME';
PROFILE RESOURCE_NAME RESOURCE LIMIT COM INH IMP
---------------------------------------------------------
AOXUSER_IDLE IDLE_TIME KERNEL 180 NO NO NO
--IDLE_TIME的值是180,单位是分钟,换算也就是3个小时
--如果修改语句如下
ALTER PROFILE AOXUSER_IDLE LIMIT IDLE_TIME 180;
- 创建新表,用于存放会话信息,这个表的设计是因为我对v$session和v$sqlarea的sql语句
CREATE TABLE session_kill_log (
SID NUMBER,
SERIAL# NUMBER,
USERNAME VARCHAR2(128),
STATUS VARCHAR2(8),
OSUSER VARCHAR2(128),
MACHINE VARCHAR2(64),
PROGRAM VARCHAR2(48),
LOGON_TIME DATE,
LAST_CALL_ET NUMBER,
SQL_TEXT VARCHAR2(1000),
CREATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE USERS;
- 添加job
BEGIN
DBMS_SCHEDULER.create_job(
job_name => 'INSERT_SESSION_SNAPSHOT_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
INSERT INTO session_kill_log (SID, SERIAL#, USERNAME, STATUS, OSUSER, MACHINE, PROGRAM, LOGON_TIME, LAST_CALL_ET, SQL_TEXT)
select sid,SERIAL#,a.username,a.STATUS,a.OSUSER,a.MACHINE,a.PROGRAM,a.LOGON_TIME,a.LAST_CALL_ET,b.SQL_TEXT
from V$SESSION a join v$sqlarea b on a.PREV_SQL_ID=b.SQL_ID where a.status = ''INACTIVE'' and a.LAST_CALL_ET>10800;
COMMIT;
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=HOURLY; BYMINUTE=0;',
enabled => TRUE,
comments => 'Job to insert session snapshot every hour'
);
END;
/
说明:
- job每个小时执行一次.
- 任务是收集会话状信息的,条件是会话状态是INACTIVE,并且空闲时间超过3小时(60603=10800)
- 表session_kill_log最后一个字段是时间戳,代表插入的时间
- 插入的数据是v$session和v$sqlarea的复合查询,所以可以查到sql信息
关于job的调度请参考:调度任务(Job Scheduling)
实现第二种方法
- 创建新表,用于存放会话信息,这个表的设计是因为我对v$session和v$sqlarea的sql语句
CREATE TABLE session_kill_log (
SID NUMBER,
SERIAL# NUMBER,
USERNAME VARCHAR2(128),
STATUS VARCHAR2(8),
OSUSER VARCHAR2(128),
MACHINE VARCHAR2(64),
PROGRAM VARCHAR2(48),
LOGON_TIME DATE,
LAST_CALL_ET NUMBER,
SQL_TEXT VARCHAR2(1000),
CREATE_TIME TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) TABLESPACE USERS;
- 创建存储过程
CREATE OR REPLACE PROCEDURE pro_kill_sess_log AS
CURSOR cut_sess IS
select sid,SERIAL#,a.username,a.STATUS,a.OSUSER,a.MACHINE,a.PROGRAM,a.LOGON_TIME,a.LAST_CALL_ET,b.SQL_TEXT
from V$SESSION a
join v$sqlarea b
on a.PREV_SQL_ID=b.SQL_ID
where a.status = 'INACTIVE' and a.LAST_CALL_ET>10800;
v_session_id NUMBER;
v_serial_number NUMBER;
v_username VARCHAR2(128);
v_status VARCHAR2(8);
v_osuser VARCHAR2(128);
v_machine VARCHAR2(255);
v_program VARCHAR2(48);
v_logon_time DATE;
v_last_call_et NUMBER;
v_sql_text VARCHAR2(1000);
BEGIN
FOR rec IN cut_sess LOOP
-- 获取当前会话的信息
v_session_id := rec.sid;
v_serial_number := rec.serial#;
v_username := rec.username;
v_status := rec.status;
v_osuser := rec.osuser;
v_machine := rec.machine;
v_program := rec.program;
v_logon_time := rec.logon_time;
v_last_call_et := rec.last_call_et;
v_sql_text := rec.sql_text;
-- 将会话信息插入日志表
INSERT INTO session_kill_log (SID, SERIAL#, USERNAME, STATUS, OSUSER, MACHINE, PROGRAM, LOGON_TIME, LAST_CALL_ET, SQL_TEXT)
VALUES (v_session_id, v_serial_number, v_username, v_status, v_osuser, v_machine, v_program, v_logon_time, v_last_call_et, v_sql_text);
-- 执行 KILL 操作
EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || v_session_id || ',' || v_serial_number || ''' IMMEDIATE';
-- 提交插入操作
COMMIT;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
-- 异常处理
ROLLBACK;
RAISE;
END pro_kill_sess_log;
/
说明:
(1). 用游标遍历我设计的sql查询语句,也就是空闲时间超过10800秒的会话。
(2). 循环遍历这些查到的信息,先将信息收集,再手动kill session
- 定时任务
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'JOB_KILL_SESSION',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN pro_kill_sess_log; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=HOURLY; INTERVAL=1', -- 每小时执行一次
enabled => TRUE
);
END;
/
关于job的调度请参考:调度任务(Job Scheduling)
补充
另一个博主的文章,写的很好:ORACLE定期清理INACTIVE会话
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库