How to kill Oracle session

Sessions can be killed from within Oracle 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

1 1 SYSTEM ORACLE.EXE

2 1 SYSTEM ORACLE.EXE

3 1 SYSTEM ORACLE.EXE

4 1 SYSTEM ORACLE.EXE

32 45 SCOTT WINSQL.EXE

37 501 SYSTEM WINSQL.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