ORACLE数据库会话有ACTIVE、INACTIVE、KILLED、CACHED、SNIPED五种状态。INACTIVE状态的会话表示此会话处于非活动、空闲、等待状态。例如PL/SQLDeveloper连接到数据库，执行一条SQL语句后，如果不继续执行SQL语句，那么此会话就处于INACTIVE状态。一般情况下,少量的INACTVIE会话对数据库并没有什么影响，如果由于程序设计等某些原因导致数据库出现大量的会话长时间处于INACTIVE状态，那么将会导致大量的系统资源被消耗，造成会话数超过系统session的最大值，出现ORA-00018:maximumnumberofsessionsexceeded错误。
有时候需要清理那些长时间处于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>=60*60*2实现，当然最好写成S.LAST_CALL_ET>=7200
3：连接到会话的程序。比如，某个特定的应用程序产生的INACTIVE会话才要清理。例如,Toad工具、PL/SQLDeveloper工具。关于PROGRAM这个需要根据当前项目的具体情况设置，下面仅仅使用TOAD.EXE、W3WP.EXE举例说明。
1:SELECTSID,SERIAL#,MODULE,STATUS
2:FROMV$SESSIONS
3:WHERES.USERNAMEISNOTNULL
4:ANDUPPER(S.PROGRAM)IN('TOAD.EXE','W3WP.EXE')
5:ANDS.LAST_CALL_ET>=60*60*2
6:ANDS.STATUS='INACTIVE'
7:ORDERBYSIDDESC;
如果是RAC环境，那么最好使用下面SQL语句，使用全局视图GV$SESSION。
1:SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
2:FROM gv$session S
3:WHERE S.USERNAME ISNOTNULL
4:ANDUPPER(S.PROGRAM) IN ('TOAD.EXE', 'W3WP.EXE')
5:AND S.LAST_CALL_ET >= 2 * 60*60
6:AND S.STATUS = 'INACTIVE'
7:ORDERBY INST_ID DESC
接下来创建存储过程SYS.DB_KILL_IDLE_CLIENTS. 方便调用该功能执行kill inactive 会话。注意：xxx部分用实际业务的PROGRAM来替代。
1:CREATEOR REPLACE PROCEDURE SYS.DB_KILL_IDLE_CLIENTS AUTHID DEFINER AS 2: job_no number; 3: num_of_kills number := 0; 4:BEGIN 5: 6:FOR REC IN 7: (SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
8:FROM gv$session S
9:WHERE S.USERNAME ISNOTNULL
10:ANDUPPER(S.PROGRAM) IN ('xxx', 'xxx.EXE')
11:AND S.LAST_CALL_ET >= 2*60*60
12:AND S.STATUS= 'INACTIVE'
13:ORDERBY INST_ID ASC
14: ) LOOP
15: ---------------------------------------------------------------------------
16: -- kill inactive sessions immediately
17: ---------------------------------------------------------------------------
18: DBMS_OUTPUT.PUT('LOCAL SID ' || rec.sid || '(' || rec.module || ')');
19:executeimmediate'alter system kill session ''' || rec.sid || ', ' || 20: rec.serial# || '''immediate' ;
21:
22: DBMS_OUTPUT.PUT_LINE('. killed locally ' || job_no);
23: num_of_kills := num_of_kills + 1;
24:END LOOP;
25: DBMS_OUTPUT.PUT_LINE ('Number of killed escmlogin_dll.exe sessions: ' || num_of_kills);
26:END DB_KILL_IDLE_CLIENTS;
27: /
28:
另外，由于killsession是直接将sessionkill掉，有可能出现导致事物回滚的现象，其实我们可以使用disconnectsession完成当前事务并终止session。这种方式比altersystemkillsession跟安全可靠。
1:CREATEOR REPLACE PROCEDURE SYS.DB_KILL_IDLE_CLIENTS AUTHID DEFINER AS 2: job_no number; 3: num_of_kills number := 0; 4:BEGIN 5: 6:FOR REC IN 7: (SELECT SID, SERIAL#, INST_ID, MODULE,STATUS
8:FROM gv$session S
9:WHERE S.USERNAME ISNOTNULL
10:ANDUPPER(S.PROGRAM) IN ('xxxx', 'xxxx')
11:AND S.LAST_CALL_ET >= 2*60*60
12:AND S.STATUS<>'KILLED'
13:ORDERBY INST_ID ASC
14: ) LOOP
15: ---------------------------------------------------------------------------
16: -- kill local sessions immediately
17: ---------------------------------------------------------------------------
18: DBMS_OUTPUT.PUT('LOCAL SID ' || rec.sid || '(' || rec.module || ')');
19:executeimmediate'alter system disconnect session ''' || rec.sid || ', ' || 20: rec.serial# || '''immediate' ;
21:
22: DBMS_OUTPUT.PUT_LINE('. killed locally ' || job_no);
23: num_of_kills := num_of_kills + 1;
24:END LOOP;
25: DBMS_OUTPUT.PUT_LINE ('Number of killed escmlogin_dll.exe sessions: ' || num_of_kills);
26:END DB_KILL_IDLE_CLIENTS;
27: /
然后，我们可以在作业（JOB）或Schedule里面定期调用该存储过程，也可以通过后台作业结合shell脚本实现定期清理空闲会话的功能。例如如下所示。
创建killSession.sh脚本，调用该存储过程SYS.DB_KILL_IDLE_CLIENTS
1:#!/bin/bash
2:
3:
4:
5: logfile=/home/oracle/cron/session/log/killSession.log
6:
7: echo " " > $logfile 2>&1
8: echo "START ----`date`" > $logfile 2>&1
9: sqlplus /nolog <> $logfile 2>&1
在crontab里面配置后台作业，每隔15分钟运行一次，清理哪些满足条件的空闲会话。
0,15,30,45 * * * * /home/oracle/cron/session/bin/killSession.sh >/dev/null 2>&1
该贴被潇湘隐者编辑于2014-3-31 22:31:47
该贴由hui.chen转至本版2014-11-5 16:24:22
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2014-3-31 22:27:34 |
赞(
操作
谢谢楼主分享
