如果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
http://askmeapps.blogspot.co.uk/2008/06/sniped-sessions-shadow-processes.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15480802/viewspace-766519/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-766519/