How To Kill Oracle Database Session

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

A.Check if the Package or table are locked using the below query :

SELECT b.object_name,

a.session_id,

a.oracle_username,

a.os_user_name,

a.process,

a.locked_mode

FROM v$locked_object a,

all_objects b

WHERE a.object_id = b.object_id

B.Get the serial number for the session based on the session id got from above qyery.

SELECT SID,

serial#,

ownerid,

status,

server,

username,

osuser,

process,

machine

FROM v$session

WHERE SID = 'Session id from above query'

C. Command to kill the session ALTER SYSTEM KILL SESSION 'Sid from query, Serial# from Query 2'

Ur's
AmarAlam