Kill Oracle process at Linux/Unix/Windows/Sqlplus

Kill Oracle Session

The SQL*Plus Approach

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;

SID SPID OSUSER     PROGRAM

---------- --------- ------------------------------ ---------------

1 310 SYSTEM ORACLE.EXE

2 300 SYSTEM ORACLE.EXE

3 309 SYSTEM ORACLE.EXE

4 299 SYSTEM ORACLE.EXE

5 302 SYSTEM ORACLE.EXE

6 350 SYSTEM ORACLE.EXE

20 412 SYSTEM DBSNMP.EXE

43 410 USER1 SQLPLUSW.EXE

33 364 USER2 SQLPLUSW.EXE

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

SQL> 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, as explained below. 

Killing OS processes is dangerous and can lead to instance failures, so do this at your own risk.It is possible to force the kill by adding the IMMEDIATE keyword:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

This should prevent you ever needing to use the orakill.exe in Windows, or the kill command in UNIX/Linux.

The NT Approach

To kill the session via the NT operating system, first identify the session as follows:

SELECT s.sid, p.spid, s.osuser, s.program FROM v$process p, v$session s WHERE p.addr = s.paddr; 

SID SPID OSUSER     PROGRAM

---------- --------- ------------------------------ ---------------

1 310 SYSTEM ORACLE.EXE

2 300 SYSTEM ORACLE.EXE

3 309 SYSTEM ORACLE.EXE

4 299 SYSTEM ORACLE.EXE

5 302 SYSTEM ORACLE.EXE

6 350 SYSTEM ORACLE.EXE

20 412 SYSTEM DBSNMP.EXE

43 410 USER1 SQLPLUSW.EXE

33 364 USER2 SQLPLUSW.EXE

The SID and SPID values of the relevant session can then be substituted into the following command issued from the command line:

C:> orakill ORACLE_SID spid

The session thread should be killed immediately and all resources released.

The UNIX Approach

To kill the session via the UNIX operating system, first identify the session in the same way as the NT approach, then substitute the relevant SPID into the following command:

$ kill -9 spid

If in doubt check that the SPID matches the UNIX PROCESSID shown using:

$ ps -ef grep ora

The session thread should be killed immediately and all resources released.