ALTER SYSTEM KILL Session Marked for Killed Forever [ID 1020720.102]

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?