Solved

kill session

Posted on 2007-11-19
4
5,192 Views
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
0
Comment
Question by:ethanjohnsons
[X]
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
4 Comments
 
LVL 35

Accepted Solution

by:
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.
0
 
LVL 8

Assisted Solution

by:LindaC
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
    abnormally.

    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;
OR
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;
Example:
- create profile idletime limit idle_time 10;

3. Attach the profile to desired user:
- alter user user_name profile profile_name;
Example:
- 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.
0
 

Expert Comment

by:vineet-kohli
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.
0
 
LVL 35

Expert Comment

by:johnsone
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.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

749 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