存在很多oracle 会话,oracle遭遇大量SNIPED会话

如果user的profile设置了non-zero的idle time，则以该用户登录的session在空闲一定时间后会变成SNIPED.

今天生产库上碰到一个案例，版本10205，processes=1200

客户说登录不上，以sysdba本地登录，

sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Jul 17 01:41:30 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected.

SQL>

典型的processes满了，杀死几个local=no的进程后才成功登陆

SQL> select status,count(*) from v$session group by status order by 2;

STATUS     COUNT(*)

-------- ----------

ACTIVE           24

INACTIVE         49

SNIPED         1124

SQL> select USERNAME,status,count(*) from v$session group by USERNAME,status order by 3;

USERNAME                       STATUS     COUNT(*)

------------------------------ -------- ----------

……

SYNCIT                         SNIPED         1120

12 rows selected.

SQL> select profile from dba_users where username='SYNCIT';

PROFILE

------------------------------

PROFILE_IDLE_TIMEOUT

SQL> select RESOURCE_NAME,LIMIT from dba_profiles where profile='PROFILE_IDLE_TIMEOUT';

RESOURCE_NAME                    LIMIT

-------------------------------- ----------------------------------------

…

IDLE_TIME                        1

…

IDLE_TIME设置为1分钟

What does 'SNIPED' status in v$session mean?

When IDLE_TIME is set in the users' profiles or the default profile. This will kill the sessions in the database (status in v$session now becomes SNIPED) and they will eventually disconnect. It does not always clean up the Unix session (LOCAL=NO sessions).

At this time all oracle resources are released but the shadow processes remains and OS resources are not released. This shadow process is still counted towards the parameters of init.ora.

This process is killed and entry from v$session is released only when user again tries to do something. Another way of forcing disconnect (if your users come in via SQL*Net) is to put the file sqlnet.ora on every client machine and include the parameter "SQLNET.EXPIRE_TIME" in it to force the close of the SQL*Net session

sqlnet.expire_time

sqlnet.expire_time actually works on a different principle and is used to detect dead connections as opposed to disconnecting(actually 'sniping') a session based on idle_time which the profile accomplishes.

But again, as you mentioned, expire_time works globally while idle_time profile works for that user. You can use both of them to make sure that the client not only gets sniped but also gets disconnected if the user process abnormally terminates.

也可写个shell脚本定时kill sniped session