How To Automate Cleanup Of Dead Connections And INACTIVE Sessions [ID 206007.1]

How To Automate Cleanup Of Dead Connections And INACTIVE Sessions [ID 206007.1]

Modified 25-JUN-2009     Type HOWTO     Status PUBLISHED


This note explains the difference between a dead connection and an 
INACTIVE session in v$session.  It also discusses the mechanisms 
provided to automate the cleanup of each.


This note is intended for any DBA who wants to automate the cleanup of
dead connections and/or INACTIVE sessions.

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.

    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 to Note:151972.1: Dead Connection Detection (DCD) Explained,
    for details regarding DCD.

(2) INACTIVE Sessions:

    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.

- The End -