kill session

Posted on 2007-11-19
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 34

Accepted Solution

johnsone earned 250 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 250 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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
JDeveloper 12c for 32 bit 4 67
Oracle RAC 12c 8 54
Not able to add the URL for the   access control list - oracle plsql 10 48
use lov values 2 18
Article by: Swadhin
From the Oracle SQL Reference ( we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

929 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now