PURPOSE
This document briefly describes how to suppress sessions marked killed in v$session.
SCOPE & APPLICATION
Useful for DBAs.
ALTER SYSTEM KILL Session Marked for Killed Forever:
====================================================
You have a session that you have killed, but it seems as though it will not go away:
SQL> alter system kill session 'sid, serial#';
SQL> select status, username from v$session;
status killed
username username
You have issued this several times and it seems it still is marked as killed.
In order to determine which process to kill:
a) On a Unix platform:
SQL> SELECT spid
FROM v$process
WHERE NOT EXISTS ( SELECT 1
FROM v$session
WHERE paddr = addr);
or
SQL> SELECT inst_id, status, event, state, blocking_session, sid, serial#, program
FROM gv$session
WHERE status = 'KILLED';
% kill <spid>
b) On a Windows platform:
SQL> SELECT spid, osuser, s.program
FROM v$process p, v$session s
WHERE p.addr=s.paddr;
Then use the orakill utility at the DOS prompt:
c:/> orakill <SID> <spid>
where <SID> = the Oracle instance name (ORACLE_SID)
<spid> = the thread id of the thread to kill
Explanation:
============
The simplest (and probably most common) reason the session stays
around is because the process is still around. The reason the
process is still around is because it is waiting on "SQLNet
message from client". If it does ever get a message, it will
then respond with an ORA-28 "Your session has been killed"
error number. At that point the session should go away. The
dedicated server process may remain alive until the client
disconnects or exits.
PMON may take ownership of the session while it is cleaning
up any resources held by the session at the time it was killed.
If it cannot clean everything up immediately it will leave the
session under the PSEUDO process while performing other tasks.
By finding the spid you can then force the process to be killed.
When issuing the 'kill' command be sure that you kill "DEDICATED SERVER PROCESSES", those called:
oracle<SID> (local=NO)
where <SID> is the ORACLE_SID.
Be sure you do not kill processes such as:
ora_d000_<SID>
ora_s000_<SID>
ora_pmon_<SID>
Related Documents:
=================
Note:100859.1
ALTER SYSTEM KILL SESSION does not Release Locks Killing a
Thread on Windows NT
Note:1041427.6
KILLING INACTIVE SESSIONS DOES NOT REMOVE SESSION ROW FROM
V$SESSION
Note:1023442.6
HOW TO HAVE ORACLE CLEAN-UP OLD USER INFO AFTER KILLING
SESSION UNDER MTS
Note:387077.1
How to find the process identifier (pid, spid) after the
corresponding session is killed?
ALTER SYSTEM KILL Session Marked for Killed Forever [ID 1020720.102]
最新推荐文章于 2022-02-23 15:30:01 发布