Go Premium for a chance to win a PS4. Enter to Win


kill session

Posted on 2007-11-19
Medium Priority
Last Modified: 2013-12-19
I found a lot of sessions are hung.  I killed the processes on AIX, but the Oracle sessions seemed to be running still.  How do you kill sessions to make sure that none of hung processes/jobs is running?

thx much
Question by:ethanjohnsons
  • 2
LVL 35

Accepted Solution

johnsone earned 750 total points
ID: 20314560
Did you kill the Oracle process at the AIX level or the program?  If you killed the Oracle process, then pmon will eventually clean up the process (depending on what the process was doing it should be less than 5 minutes).  If you did not kill the Oracle process, then you can use:

ALTER SYSTEM KILL SESSION '<sid>,<serial#>';

You can get the values for sid and serail# from the V$SESSION view.

Assisted Solution

LindaC earned 750 total points
ID: 20314955
>> Note 151972.1 Dead Connection Detection (DCD) Explained
>> Note 16420.1 Changing the default TCP/IP Keepalive Interval
>> Note 390279.1 Modifying Kernel Parameters on RHEL, SLES, and Oracle Enterprise Linux using sysctl

>>Note 206007.1
Title: How To Automate Cleanup Of Dead Connections And INACTIVE Sessions

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

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

. Enable the feature
- alter system set resource_limit = true;
Set the parameter in the parameter file and restart the database.

2. Create the profile for monitoring idle time:
- create profile profile_name limit idle_time no_of_minutes;
- create profile idletime limit idle_time 10;

3. Attach the profile to desired user:
- alter user user_name profile profile_name;
- alter user scott profile idletime;

This forces any Oracle user sessions, which have been inactive for greater than
no_of_minutes, to be disconnected from the database. Any uncommitted
transaction will be rolled back. When the idle time has passed, the session
will be suspended. Next time the user enters a command he will receive an ORA-
02396: exceeded maximum idle time, please connect again.

CD is initiated on the server when a connection is established. At this
time SQL*Net reads the SQL*Net parameter files and sets a timer to generate an
alarm.  The timer interval is set by providing a non-zero value in minutes for
the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file.

When the timer expires, SQL*Net on the server sends a "probe" packet to the
client. (In the case of a database link, the destination of the link
constitutes the server side of the connection.)  The probe is essentially an
empty SQL*Net packet and does not represent any form of SQL*Net level data,
but it creates data traffic on the underlying protocol.

Expert Comment

ID: 22487287
We have a web connectivity to an AIX oracle server.When  the session increased,the 'Alter system kill' command failed to kill. When the related web server was made shutdown,same command showed,that the sessions referred does not exist.However the session are still shown in session view command.Pls guide to,how to remove the sessions.
LVL 35

Expert Comment

ID: 22487741
You should open a new question.

If the alter system fails to kill the process, you can select the process id of the process from v$process.

select spid from v$process a, v$session b where a.addr = b.paddr and b.sid = <sid>;

Then you can kill the process at the OS level.

Either way the process is killed, you need to be prepared to wait up to 5 minutes for pmon to clean up the process (pmon only wakes up every 5 minutes).  Also, any active needs to be rolled back and that takes time.

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question