【Oracle】How To Automate Cleanup Of Dead Connections And INACTIVE

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

Modified 25-JUN-2009     Type HOWTO     Status PUBLISHED

PURPOSE
-------

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.

SCOPE & APPLICATION
-------------------

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
    abnormally.

    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 : 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.

    : How To Automate Disconnection of Idle Sessions,
    outlines the steps to setup IDLE_TIME for this.