Oracle中session定期清理空闲连接

参考资料：ORACLE会话连接进程三者总结                  

                  ORACLE快速彻底Kill掉的会话

---

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系统视图才能看到相关信息，有兴趣可以使用这个工具包实现定时任务。