Monday, June 24, 2013

How To Kill Oracle Database Session

First identify the offending sessions using active running processes query :

The SQL*Plus Approach
-------------------------------
Sessions can be killed from within oracle using the ALTER SYSTEM KILL SESSION syntax.

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 peril.

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

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
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

note:-sid is different from ORACLE_SID

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect