[Oracle] ALTER SYSTEM KILL Session Marked for Killed Forever

제목:  ALTER SYSTEM KILL Session Marked for Killed Forever

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, but has a lock on the existing table.

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

% 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:
============

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>