参考资料:ORACLE会话连接进程三者总结
session状态说明
会话有ACTIVE、INACTIVE、KILLED、CACHED、SNIPED五个状态,一般比较常见的有ACTIVE、INACTIVE、KILLED三个状态。
ACTIVE :处于此状态的会话,表示正在执行,处于活动状态。
INACTIVE :处于此状态的会话表示不是正在执行的
KILLED :处于此状态的会话,表示出现了错误或进程被杀掉,正在回滚,当然,这个状态的会话也占用系统资源的。还有一点就是, KILLED的状态一般会持续较长时间,如果你想快速杀掉回话,可以参考ORACLE快速彻底Kill掉的会话
CACHED : Session temporarily cached for use by Oracle*XA
SNIPED : Session inactive, waiting on the client。 标记为SNIPED的进程被释放有两种条件:
1、相关的terminal再一次试图登录及执行sql
2、手动的在操作系统后台kill掉相应的spid
设置session数和process连接数
查询数据库允许的最大会话数和进程数:
select value from v$parameter where name = 'processes';
select value from v$parameter where name = 'sessions';只要会话连接数超过上面的process数或者sessions数,再来一个的会话进程,就会产生ORA-12516错误。
查看进程,会话的历史最大数和最大数:
select resource_name,max_utilization,limit_value from v$resource_limit where resource_name in('processes','sessions');修改会话数和连接数:
注意一下processes的值和sessions的值,Oracle官方文档中要求sessions=processes*1.5+5
alter system set processes=1000 scope=spfile;
alter system set sessions=1105 scope=spfile;重新启动数据库服务即可!
调整数据库用户最大空闲连接时间idle_time
参考资料:Oracle 概要文件IDLE_TIME限制用户最大空闲连接时间
select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where PROFILE='DEFAULT';
select username,profile from dba_users where username='username';
select PROFILE,RESOURCE_NAME,LIMIT from dba_profiles where RESOURCE_NAME='IDLE_TIME';
select name,value from gv$parameter where name='resource_limit';
alter system set resource_limit=true;--开启数据库资源使用
alter profile default limit idle_time 60;--设置空闲时间
设置了连接的空闲时间后,通过idle_time限制session idle 时间。session idle超过设置时间,状态为sniped (v$session),然而OS下的process并不会释放,当session(user process) 再次与server process 通讯,提示连接已经超时,将关闭相应的server process。
实际并没有关闭回收相应的session,只是把会话状态标记为sniped (被限制资源的状态),有文档说配置SQLNET.EXPIRE_TIME:对于SQLNET.EXPIRE_TIME的配置,需要修改sqlnet.ora,其路径为$ORACLE_HOME/network/admin下面。然后添加SQLNET.EXPIRE_TIME=10,之后重启监听。经过实测该方法并未生效,所以需要手动回收掉。
设置定时任务回收超期的session
参考资料:Oracle session连接数和inactive的问题记录(清除方法)
ORACLE定期清理INACTIVE会话
PLSQL创建Oracle定时任务
-
创建清理过期需要回收的session的存储过程
注意以sys角色登录操作!
CREATE OR REPLACE NONEDITIONABLE PROCEDURE DB_KILL_IDLE_CLIENTS AUTHID DEFINER AS
job_no number;
num_of_kills number := 0;
BEGIN
FOR REC IN
(SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
FROM gv$session S
WHERE S.USERNAME IS NOT NULL
-- AND UPPER(S.PROGRAM) IN('xxxx', 'xxxx')
-- 空闲时间超过12小时的连接
AND S.LAST_CALL_ET >= 12*60*60
AND S.STATUS<>'KILLED'
ORDER BY INST_ID ASC
) LOOP
DBMS_OUTPUT.PUT('LOCAL SID ' || rec.sid || '(' || rec.module || ')');
execute immediate 'alter system disconnect session ''' || rec.sid || ', ' ||
rec.serial# || '''immediate' ;
DBMS_OUTPUT.PUT_LINE('. killed locally ' || job_no);
num_of_kills := num_of_kills + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Number of killed system sessions: ' || num_of_kills);
END DB_KILL_IDLE_CLIENTS;
手动执行存储过程脚本:
begin
--设置buffer_size大小不受限,防止过多的连接导致堆栈异常
DBMS_OUTPUT.ENABLE (buffer_size=>null);
-- Call the procedure
DB_KILL_IDLE_CLIENTS;
end;
-
通过pl/sql创建定时清理任务
设置完点击应用。
相关sql如下:
查看定时任务执行情况:select job,last_date,last_sec,next_date,next_sec,broken,failures from dba_jobs;
注意,如果next_date是4000-1-1表示这个脚本已经是停止状态。
删除定时任务:
exec dbms_job.remove(任务编号);
扩展说明:
网上有资料显示,现在oracle创建job有两种方式,dbms_job是比较老的方式,现在已过时,比较智能的是dbms_scheduler包创建,这个10g以后提供的,要查询dba_scheduler_xxxxx系统视图才能看到相关信息,有兴趣可以使用这个工具包实现定时任务。