合 在Oracle中，如何定时清理INACTIVE状态的会话（配置会话的空闲时间）
Tags： OracleDCD定时清理INACTIVE空闲会话空闲时间
简介
ORACLE数据库会话有ACTIVE、INACTIVE、KILLED、 CACHED、SNIPED五种状态。INACTIVE状态的会话表示此会话处于非活动、空闲、等待状态。例如PL/SQL Developer连接到数据库，执行一条SQL语句后，如果不继续执行SQL语句，那么此会话就处于INACTIVE状态。一般情况下,少量的INACTVIE会话对数据库并没有什么影响，如果由于程序设计等某些原因导致数据库出现大量的会话长时间处于INACTIVE状态，那么将会导致大量的系统资源被消耗，造成会话数超过系统session的最大值，出现ORA-00018:maximum number of sessions exceeded错误。
有时候需要清理那些长时间处于INACTIVE状态的会话。人为定期检查、杀掉这类会话肯定不太现实，要定期清理那些长时间处于INACTIVE的会话，只能通过作业来实现；另外需要注意，Kill掉这些会话需要需要谨慎，稍不注意，就有可能误杀了一些正常的会话。那么我们该如何定义这类会话呢？下面是我结合业务规则定义的：
1： 会话的Status必须为INACTIVE，如果会话状态为ACTIVE、KILLED、CACHED、SNIPED状态，不做考虑。
2： 会话必须已经长时间处于INACTIVE状态。例如，处于INACTIVE状态超过了两小时的会话进程，才考虑Kill。这个视具体业务或需求决定，有可能超过半小时就可以杀掉会话进程。至于如何计算处于INACTIVE会话状态的时间，这个可以 通过V$SESSION的LAST_CALL_ET字段来判别，需要查询处于INACTIVE状态两小时或以上的会话，就可以通过查询条件S.LAST_CALL_ET >= 60602实现，当然最好写成 S.LAST_CALL_ET >= 7200
3： 连接到会话的程序。比如，某个特定的应用程序产生的INACTIVE会话才要清理。例如, Toad工具、PL/SQL Developer工具。关于PROGRAM这个需要根据当前项目的具体情况设置
一般情况下，少量的INACTVIE会话对数据库并没有什么影响，但是，如果由于程序设计等某些原因导致数据库出现大量的会话长时间处于INACTIVE状态，那么将会导致大量的系统资源被消耗，造成会话数超过系统SESSION的最大值，出现ORA-00018:maximum number of sessions exceeded错误。此时就需要清理那些长时间处于INACTIVE状态的会话。人为定期检查、杀掉这类会话肯定不太现实，要定期清理那些长时间处于INACTIVE的会话，可以使用如下几种办法：
Oracle 12之下版本
方法1
设置用户profile的IDLE_TIME参数，需要设置resource_limit为true，然后再设置IDLE_TIME参数，单位为分钟：
1 2 | alter system set resource_limit=true; alter profile default limit idle_time 10; |
该方法需要在sqlnet.ora文件里加上sqlnet.expire_time=5，单位为分钟数。
查询：
1 2 3 4 5 6 | select * from dba_profiles d where d.resource_name='IDLE_TIME'; select u.username,d.profile, d.resource_name ,d.limit from dba_users u , dba_profiles d where u.PROFILE=d.profile and d.resource_name='IDLE_TIME' and U.username='LHR'; alter profile ORA_STIG_PROFILE limit IDLE_TIME UNLIMITED; |
方法2
直接KILL掉INACTIVE的会话。V$SESSION视图中的LAST_CALL_ET字段表示用户最后一条语句执行完毕后到sysdate的时间，单位为秒。每次用户执行一个新的语句后，该字段复位为0，重新开始记数。可以通过该字段来获得一个连接用户最后一次操作数据库后的空闲时间。推荐使用这种方法来释放INACTIVE状态的会话。具体代码如下所示：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | set sqlblanklines on CREATE OR REPLACE PROCEDURE P_kill_session_LHR AS ----------------------------------------------------------------------------------- -- Created on 2013-06-25 12:05:07 by lhr --Changed on 2015-08-05 12:05:07 by lhr -- function： 杀掉10个小时之前的会话 ,告警日志中会记录被杀掉的会话信息 ----------------------------------------------------------------------------------- BEGIN -- IF to_char(SYSDATE, 'HH24') >= '20' OR -- TO_CHAR(SYSDATE, 'HH24') <= '08' THEN FOR cur IN (SELECT A.USERNAME, A.LOGON_TIME, A.STATUS, A.SID, A.SERIAL#, A.MACHINE, A.OSUSER, 'ALTER SYSTEM DISCONNECT SESSION ''' || a.SID || ',' || a.serial# || ',@' || a.INST_ID || ''' IMMEDIATE' kill_session FROM gv$session A WHERE A.STATUS IN ('INACTIVE') AND A.USERNAME IS NOT NULL AND A.LAST_CALL_ET >= 60 * 60 * 10) LOOP BEGIN EXECUTE IMMEDIATE cur.kill_session; EXCEPTION WHEN OTHERS THEN NULL; END; END LOOP; -- END IF; EXCEPTION WHEN OTHERS THEN NULL; END P_kill_session_LHR; / BEGIN --DBMS_SCHEDULER.drop_job('JOB_P_kill_session_LHR'); DBMS_SCHEDULER.CREATE_JOB(JOB_NAME => 'JOB_P_kill_session_LHR', JOB_TYPE => 'STORED_PROCEDURE', JOB_ACTION => 'P_kill_session_LHR', repeat_interval => 'FREQ=MINUTELY;INTERVAL=60', ENABLED => TRUE, START_DATE => SYSDATE, COMMENTS => '删除--每60分钟检查一次'); END; / |
12.2新参数MAX_IDLE_TIME和MAX_IDLE_BLOCKER_TIME
1 2 3 4 5 6 7 | LHR@127.0.0.1/orclpdb1> show parameter idle NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ max_idle_blocker_time integer 0 max_idle_time integer 0 LHR@127.0.0.1/orclpdb1> |
Oracle 12.2 引入了新参数MAX_IDLE_TIME。它可以指定会话空闲的最大分钟数。如果会话空闲的时间超过了这个阈值的话，这个会话将会被自动终止。其实在Oracle 10g& 11g时代，我还写过脚本定期清理INACTIVE会话，当时写的文章名为ORACLE定期清理INACTIVE会话”。从Oracle 12.2开始，就完全没有必要这样做了，设置一个简单的参数即可解决这个问题，见微知著，一叶知秋。以后数据库运维的趋势确实是越来越简单化,自动化。
MAX_IDLE_TIME这个参数的时间单位是分钟，注意不是秒。可以在PDB级别或CDB级别修改。但是不能在会话级别修改（ALTER SESSION），另外，对于RAC实例，不同节点的值可以设置成不一样。如果参数MAX_IDLE_TIME的值为0，表示不限制会话的空闲时间。当会话的空闲时间超过阈值时，会话被终止后，你会在客户端收到ORA-03113错误。注意，有时候我们也会通过RESOURCE_LIMIT限制会话最大的空闲时间，通过在PROFILE里面设置IDLE_TIME的值来实现，如果是通过这种方式来终止会话的话，收到的错误为“ORA-02396: exceeded maximum idle time, please connect again”。注意两者的区别。
