Page: 1 2

One of the most common mistakes done by most Oracle database administrators is killing the wrong session. Every database administrator must carefully review the session before killing it. The process and SQL associated with the offensive task must also be analyzed. The database administrator may decide to view the SQL being executed by the session before killing the session.

Killing session with Oracle is a challenge for every database administrator. Many different procedures exist within Windows and UNIX for this purpose.

Killing Oracle Sessions:

You can follow below steps to kill an Oracle session.

• Gather session information from Oracle
• Kill the session at the operating system level
• You can kill the session within Oracle using the "alter system kill session" command in SQL*Plus. First you have to identify the session to be killed.

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

SID SERIAL# OSUSER PROGRAM
-----------------------------
1 1 SYSTEM ORACLE.EXE
2 1 SYSTEM ORACLE.EXE
3 1 SYSTEM ORACLE.EXE
4 1 SYSTEM ORACLE.EXE
5 1 SYSTEM ORACLE.EXE
6 1 SYSTEM ORACLE.EXE
20 60 SYSTEM DBSNMP.EXE
43 11215 USER1 SQLPLUSW.EXE
33 5337 USER2 SQLPLUSW.EXE

The SID and SERIAL# values of the relevant session can then be substituted.

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

Sometimes Oracle.exe is not able to kill the session immediately and the session will be ‘marked for kill'. It will then be killed as soon as possible.

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

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

• For killing sessions in UNIX you have to locate the Server PID (SPID) from v$process and issue the UNIX kill -9 command.

% kill -9 spid

Incase of doubt you can check that the SPID matches the UNIX PROCESSID by using below command.

% ps -ef | grep ora

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

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle: A Guide to determine Best Execution Plan
   Expert Tips on Drop Temporary Tablespace Hangs!!
   Important Oracle Issues and Solutions: A Must Read!!
   A Quick Guide to determine Oracle RAM Size!!