在Oracle中，如何定时清理INACTIVE状态的会话（配置会话的空闲时间）
Tags： DCDINACTIVEOracle定时清理空闲会话空闲时间
简介
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”。注意两者的区别。
另外，我们来看另外一个参数MAX_IDLE_BLOCKER_TIME ，这个参数最开始宣称是ORACLE 21c引入的新特性，但是实际上ORACLE 19c已经有这个参数了。它定义了阻塞会话的最大会话空闲时间，以分钟为单位。默认值 0 也表示没有限制。官方文档[2]关于这个参数的介绍如下：
1 2 3 4 5 6 7 | A session is considered to be a blocking session when it is holding resources required by other sessions. For example: - The session is holding a lock required by another session. - The session is a parallel operation and its consumer group, PDB, or database has either reached its maximum parallel server limit or has queued parallel operations. - The session’s PDB or database instance is about to reach its SESSIONS or PROCESSES limit. This parameter differs from the MAX_IDLE_TIME parameter in that MAX_IDLE_TIME applies to all sessions (blocking and non-blocking), whereas MAX_IDLE_BLOCKING_TIME applies only to blocking sessions. Therefore, in order for MAX_IDLE_BLOCKING_TIME to be effective, its limit must be less than the MAX_IDLE_TIME limit. |
当会话持有其它会话所需的资源时，该会话被视为阻塞会话. 例如
- 该会话持有另一个会话所需的锁。
- 该会话是并行操作，并且其使用者组，PDB或数据库已达到其最大并行服务器限制或已排队的并行操作。
- 会话的PDB或数据库实例即将达到其SESSIONS或PROCESSES限制。
这个参数与MAX_IDLE_TIME参数的不同之处在于，MAX_IDLE_TIME适用于所有会话（阻塞和非阻塞），而MAX_IDLE_BLOCKING_TIME仅适用于阻塞会话。 因此，为了使MAX_IDLE_BLOCKING_TIME有效，其限制必须小于MAX_IDLE_TIME限制。
注意事项：
此参数对并行查询进程和 SYS 用户会话都没有影响。所以你不要用sys用户去测试，否则你会发现它不生效。
This parameter does not have an effect on parallel query processes, nor on SYS user sessions.
此参数从Oracle 19c开始就已经提供了。不是从Oracle 21c开始。
This parameter is available starting with Oracle Database 19c.
有了这个参数，你又不用写脚本去kill阻塞会话了。你看，新增的一个功能/特性就能节省你很多工作量。但是这个功能也还有一些不足的地方：DBA不清楚Kill了哪些会话，阻塞会话最后执行过什么SQL也无从得知，如果是自己写的脚本，往往可以记录这些信息，方便事后我们回溯、分析问题。如果Oracle在终止会话的同时，将这些信息写入trace文件或数据字典，那就相当完美了。
如何查询超过10小时无响应的会话？
GV$SESSION视图的LAST_CALL_ET字段表示客户端无相应的时间，可以根据该字段来查询，具体SQL语句如下所示：
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 | SELECT A.INST_ID, A.USERNAME, A.LOGON_TIME, A.STATUS, A.SID, A.SERIAL#, (SELECT NB.SPID FROM GV$PROCESS NB WHERE NB.ADDR = A.PADDR AND NB.INST_ID = A.INST_ID) SPID, (SELECT TRUNC(NB.PGA_USED_MEM / 1024 / 1024) FROM GV$PROCESS NB WHERE NB.ADDR = A.PADDR AND NB.INST_ID = A.INST_ID) PGA_USED_MEM, (A.MODULE || '--' || A.ACTION || '--' || A.PROGRAM || '--' || A.CLIENT_IDENTIFIER || '--' || A.CLIENT_INFO || '--' || A.SERVICE_NAME) SESSION_TYPE, A.OSUSER, ROUND(A.LAST_CALL_ET / 60 / 60, 2) TOTAL_H, 'ALTER SYSTEM DISCONNECT SESSION ''' || A.SID || ',' || A.SERIAL# || ''' IMMEDIATE' KILL_SESSION FROM GV$SESSION A WHERE A.STATUS IN ('INACTIVE') AND A.USERNAME IS NOT NULL AND A.USERNAME NOT IN ('SYS') AND A.LAST_CALL_ET >= 60 * 60 * 10 ORDER BY A.INST_ID, A.LAST_CALL_ET DESC, A.USERNAME, A.LOGON_TIME; |
参考
https://mp.weixin.qq.com/s/4EI3UhFJMmJbOPWFLf-hnA
