oracle sniped session

最新推荐文章于 2023-08-03 10:45:21 发布

转载最新推荐文章于 2023-08-03 10:45:21 发布 · 185 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/zhiyonghe/blog/2243883

文章标签：

#数据库 #python

本文详细解释了Oracle数据库中'SNIPED'状态的含义及其如何影响数据库会话。当用户配置了IDLE_TIME参数，长时间未操作的会话将被标记为'SNIPED'，虽然Oracle资源被释放，但OS资源可能仍被占用。通过调整用户配置文件中的idle_time参数或SQLNET.EXPIRE_TIME参数，可以强制断开闲置会话。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019独角兽企业重金招聘Python工程师标准>>>

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.

Oracle has several ways to disconnect inactive or idle sessions, both from within SQL*Plus via resources profiles (connect_time, idle_time), and with the SQL*net expire time parameter. Here are two ways to disconnect an idle session:

Set the idle_time parameter in the user profile

Set the sqlnet.ora parameter expire_time.

Here is an alter/create profile command to set the idle_time to 1,800 seconds, at which time the session will be marked as sniped:

alter profile senior_claim_analyst limit
idle_time 1800;

A sniped session is marked for eventual killing by the PMON background process, but in the meantime, the sniped session still exists as a Oracle session and for dedicated sessions (non shared servers), a sniped session has an OS PID that can be seen with a "ps â€“ef|grep" command.

You can use scripts to killed sniped sessions by nuking the Oracle session with akill session and nuking the OS PID.

There are also enhanced ways to kill a sniped session starting in Oracle 11g.

This v$session query will show details for all sniped sessions:

select
   a.sid,
   a.serial#,
   b.sql_text
from
   v$session a,
   v$sqlarea b
where
   a.sql_address=b.address
and
   a.status= 'SNIPED';