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
0 comments:
Post a Comment