这篇文章解释了死连接（dead connection）和 v$session中的 非活动会话（inactive）
范围 & 应用
Difference between INACTIVE sessions and Dead Connections
Dead connections and INACTIVE sessions are different issues. Oracle
provides separate mechanisms to automate the cleanup of each.
(1) Dead connections:
These are previously valid connections with the database but the
connection between the client and server processes has terminated
Examples of a dead connection:
– 一个用户 重启/关机 了他的电脑，而之前并没有执行注销或者断开与数据库的连接
– A user reboots/turns-off their machine without logging off
or disconnecting from the database.
– A network problem prevents communication between the client
and the server.
用Net8（其实就是SQL*Net）里面的DCD（Dead Connection Detection）特性可以自动清理这样的会话。
In these cases, the shadow process running on the server and the
session in the database may not terminate. To automate the cleanup
of these sessions, you can use the Dead Connection Detection (DCD)
feature of Net8.
When DCD is enabled, Net8 (server-side) sends a packet to the client.
If the client is active, the packet is discarded. If the client has
terminated, the server will receive an error and Net8 (server-side)
will end that session.
Refer toNote:151972.1: Dead Connection Detection (DCD) Explained,
for details regarding DCD.
(2) INACTIVE Sessions:
有些仍然与数据库相连接的会话在 v$session 里的status 显示为INACTIVE。
These are sessions that remain connected to the database with a
status in v$session of INACTIVE.
Example of an INACTIVE session:
一个用户发起了 一个 程序/会话，然后让它运行或者空间了一段延长的时间。
– A user starts a program/session, then leaves it running and idle
for an extended period of time.
To automate cleanup of INACTIVE sessions you can create a profile
with an appropriate IDLE_TIME setting and assign that profile to
the users.Note:159978.1: How To Automate Disconnection of Idle Sessions,
outlines the steps to setup IDLE_TIME for this.
情况(1)： 编辑 sqlnet.ora文件，新增expire_time=x (单位分钟)
情况(2)： create profile idleprofile limit idle_time=10;
alter user testuser profile idelprofile ;
alter system set RESOURCE_LIMIT= true;
A sniped session occurs when a user has exceeded his idle time. The situation has been noted in the database, and the user’s actual database session has been suspended. However, the user is still consuming a dedicated server resource that has not yet been allocated to someone else. This situation can have an adverse effect on overall database performance.
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 alloracleresources 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.
A status of SNIPED in v$session for a session indicates that the session has been terminated by Oracle, because it exceeded the IDLE_TIME set in the profile assigned to the user. For a session to be terminated in such a manner, the RESOURCE_LIMIT parameter should be set to true. All locks and
resources held by the session get released during this SNIPE.
The problem with this encountered by yours truly in the past, is that, this session does not get deleted from v$sesssion. This could build up until you get a Number of sessions exceeded Oracle error, in which case the only solution is to bounce the database. This entry will get deleted from v$session only if Oracle responds back to the user saying his or her session has been terminated, which in most cases, happens the next morning
Inactive sessions in the database.
Following a conversation regarding inactive sessions and the process limitation on the database, this client was having connection failures to the database. What should be done regarding this ?
There are several things that can be done to help this.
First off, check what the sessions are doing. There are many session which remain marked as inactive, but are they executing lots of small pieces of code, and your query is finding only when they are inactive.
For that, look to the LAST_CALL_ET column of v$session which will indicate the last time the session was active. If the sessions have remained inactive for a long period of time, then these are indeed not being dropped. From that you can refer to metalink note 206007.1 (How To Automate Cleanup Of Dead Connections And INACTIVE Sessions).
Check how the connections are made, for example and application server may be creating new connections for each and every connection coming in through the server. If this is the case then you should be enabling connection pooling on the server to prevent the overload of the number of sessions connected to the database. My experience would be that with a high number of connections being maintained by an application server would normally bring this system down.
You can configure systems to have a max set of connections, and a minimum level, whereby the server can open for example 20 sessions in the pool, and obtain more as required up to a defined upper limit.
Some applications may not be killing sessions created to the database and simply creating more as required. This would be a bug in the application level, and should be investigated.
Ensure also that if the sessions parameter has been increased, that the processes parameter is also incremented, whilst bearing in mind that the database requries a restart each time this has to be changed. You will require more processes than sessions to cater for the background processes running on the database (Check v$process for these item).
As a last resort, you can implement a process which kills the inactive sessions, but beware that the session is not part of a pool of connections which you are killing. Many application require to be restarted to re-establish the pool of connections. Ensure you are looking at sessions which are not just idle, but have not communicated with the database is a reasonable length of time.