oracle session inactive killed 批量清理

 上个星期开始，oracle SESSION 老是达到最大连接数，查看oracle SESSION 发现有100多个inactiveSESSION.

可能是由于WEBSPHERE最近老是内存javacore导致websphere出现重启现象。

 

以下的SQL语句列出当前数据库建立的会话情况:

select sid,serial#,username,program,machine,status from v$session;

 

      SID    SERIAL# USERNAME                       PROGRAM                                                          MACHINE                                                          STATUS
---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- --------
         1          1                                ORACLE.EXE                                                       PC-200903311241                                                  ACTIVE
         2          1                                ORACLE.EXE                                                       PC-200903311241                                                  ACTIVE
         3          1                                ORACLE.EXE                                                       PC-200903311241                                                  ACTIVE
         4          1                                ORACLE.EXE                                                       PC-200903311241                                                  ACTIVE
         5          1                                ORACLE.EXE                                                       PC-200903311241                                                  ACTIVE
         6          1                                ORACLE.EXE                                                       PC-200903311241                                                  ACTIVE
         7          1                                ORACLE.EXE                                                       PC-200903311241                                                  ACTIVE
         8          1                                ORACLE.EXE                                                       PC-200903311241                                                  ACTIVE
         9         14 BASE_BJ                        JDBC Thin Client                                                 PC-200903311241                                                  INACTIVE
        10        585 SYS                            plsqldev.exe                                                     WORKGROUP/BOXING10                                               INACTIVE
        13        220 DRV_BX_2010_EXAM               plsqldev.exe                                                     WORKGROUP/XZ                                                     INACTIVE
        14         10 VIO_ADMIN                      JDBC Thin Client                                                 PC-200903311241                                                  INACTIVE
        15         21 VIO_ADMIN                      JDBC Thin Client                                                 PC-200903311241                                                  INACTIVE
        16         95 SYS                            plsqldev.exe                                                     WORKGROUP/BOXING10                                               ACTIVE
        18        374 DRV_BX_2010_EXAM                                                                                xz                                                               INACTIVE
        19        193 DRV_BX_2010_EXAM               plsqldev.exe                                                     WORKGROUP/XZ                                                     INACTIVE
        20        281 SYS                            plsqldev.exe                                                     WORKGROUP/BOXING10                                               INACTIVE
        21        201 DRV_BX_2010_EXAM               plsqldev.exe                                                     WORKGROUP/XZ                                                     ACTIVE
        22        267 SYS                            plsqldev.exe                                                     WORKGROUP/BOXING10                                               INACTIVE
        29        248 DRV_BX_2010_EXAM               plsqldev.exe                                                     WORKGROUP/XZ                                                     ACTIVE
 
       SID    SERIAL# USERNAME                       PROGRAM                                                          MACHINE                                                          STATUS
---------- ---------- ------------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- --------
        31        354 DRV_BX_2010_EXAM               plsqldev.exe                                                     WORKGROUP/XZ                                                     INACTIVE
        32         53 BASE_BJ                        JDBC Thin Client                                                 PC-200903311241                                                  INACTIVE
        33        720 VIO_ADMIN                                                                                       BOXING12                                                         INACTIVE

 

其中，

SID 会话(session)的ID号;

SERIAL# 会话的序列号，和SID一起用来唯一标识一个会话;

USERNAME 建立该会话的用户名;

PROGRAM 这个会话是用什么工具连接到数据库的;

STATUS 当前这个会话的状态，ACTIVE表示会话正在执行某些任务，INACTIVE表示当前会话没有执行任何操作;

如果DBA要手工断开某个会话，则执行:

alter system kill session 'SID,SERIAL#' ;

或

alter system kill session 'SID,SERIAL#' immediate;  不会产生KILLED SESSION

注意，上例中SID为1到8(USERNAME列为空)的会话，是Oracle的后台进程，不要对这些会话进行任何操作。

 

从各处收集了一些查看当前会话的语句和杀session的方法，记录一下：

1.select count(*) from v$session；
select count(*) from v$process；
查看当前总会话数和进程数，这两个视图就是跟会话及进程有关的重要视图啦，信息都是从这里面取的。

2.查询那些应用的连接数此时是多少
select b.MACHINE, b.PROGRAM , count(*) from v$process a, v$session b where a.ADDR = b.PADDR and b.USERNAME is not null   group by b.MACHINE , b.PROGRAM order by count(*) desc;

3.查询是否有死锁
select * from v$locked_object;
如果查询结果为no rows selected，说明数据库中没有死锁。否则说明数据库中存在死锁。


接下来说明一下会话的状态：
1.active 处于此状态的会话，表示正在执行，处于活动状态。
2.killed 处于此状态的会话，表示出现了错误，正在回滚，当然，也是占用系统资源的。还有一点就是，killed的状态一般会持续较长时间，而且用windows下的工具pl/sql developer来kill掉，是不管用的，要用命令：alter system kill session 'sid,serial#' ;
3.inactive 处于此状态的会话表示不是正在执行的，比如select语句已经完成。我一开始以为，只要是inactive状态的会话，就是该杀，为什么不释放呢。其实，inactive对数据库本身没有什么影响，但是如果程序没有及时commit，那么就会造成占用过多会话。解决inactive的方法最好的就是在oracle中直接设置超时时间，也是有两种方法，区别暂时还不清楚：

1.修改sqlnet.ora文件，新增expire_time=x(单位是分钟)  
我的sqlnet.ora位置在D:/oracle/ora92/network/admin

2.通过ALTER PROFILE DEFAULT LIMIT IDLE_TIME 10; 命令修改,记得重启下oracle。

 

==========

 

 自动杀的JOB