Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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 Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

721 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