KILL Session Marked for Killed Forever

Do you have a session that you have killed, but it seems not to go away ?

alter system kill session 'sid, serial#';
select status, username from v$session;

You issued this several times and it seems it still is marked for killed, but has a lock on the existing table.

In order to determine which process ID to kill:
a) On Unix:

SELECT SPID FROM V$PROCESS WHERE NOT EXISTS ( SELECT 1 FROM V$SESSION WHERE PADDR = ADDR);
% kill spid

b) On Windows:

SELECT SPID, OSUSER, S.PROGRAM FROM V$PROCESS P, V$SESSION S WHERE P.ADDR=S.PADDR;

Then use the orakill utility at the cmd prompt: c:\> orakill <SID> <spid>

where <SID> = the Oracle instance name (ORACLE_SID) <spid> = the thread id of the thread to kill

Explanation: PMON has not yet cleaned up the process and this may not happen for several hours. By finding the spid you can then force the process to be killed instead of waiting for PMON to wake up and kill it. 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>

Advertisements