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

http://askmeapps.blogspot.co.uk/2008/06/sniped-sessions-shadow-processes.html