• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5220
  • Last Modified:

kill session

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
  • 2
2 Solutions
johnsoneSenior Oracle DBACommented:
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.
>> 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.
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.
johnsoneSenior Oracle DBACommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now