How To Kill/terminate A Session In Oracle - Synametrics Technologies

Home » How To Kill Session In Oracle » How To Kill/terminate A Session In Oracle - Synametrics Technologies

Maybe your like

WinSQL » Knowledge base

Document information

Document ID: 768
Subject: How to kill/terminate a session in Oracle
Creation date: 7/29/09 2:57 PM
Last modified on: 7/29/09 2:57 PM
Details

Sessions can be killed from within WinSQL using the ALTER SYSTEM KILL SESSION syntax.

First identify the offending session as follows:

SELECT s.sid, s.serial#, s.osuser, s.program FROM v$session s;

This query will result in a similar result.
SID SERIAL# OSUSER PROGRAM
11SYSTEMORACLE.EXE
21SYSTEMORACLE.EXE
31SYSTEMORACLE.EXE
41SYSTEMORACLE.EXE
3245SCOTTWINSQL.EXE
37501SYSTEMWINSQL.EXE

The SID and SERIAL# values of the relevant session can then be substituted into the following statement:

ALTER SYSTEM KILL SESSION 'sid,serial#'

In some situations the Oracle.exe is not able to kill the session immediately. In these cases the session will be "marked for kill". It will then be killed as soon as possible.

Issuing the ALTER SYSTEM KILL SESSION command is the only safe way to kill an Oracle session. If the marked session persists for some time you may consider killing the process at the operating system level. However, this is not recommended as it is dangerous and can lead to instance failures.

It is possible to force the kill by adding the IMMEDIATE keyword:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE

User comments

Posted by no good on 11/12/15 1:24 AMI can't kill the active session using the IMMEDIATE keyword

Posted by vasan on 3/5/11 12:13 AMi am not a DBA. so i could not kill the session as the session is hanged. please tell me how to kill an user session

Posted by harsha on 2/18/13 7:28 AMhow to know,which session is for which operation?

Add a comment to this document

Do you have a helpful tip related to this document that you'd like to share with other users?

Important: This area is reserved for useful tips. Therefore, do not post any questions here. Instead, use our public forums to post questions. Your Name Email Hide my email address Captcha Your Tip Submit Tip

Tag » How To Kill Session In Oracle