Monday, October 7, 2013

ALTER SYSTEM KILL Session Marked for Killed Forever


We have a many  session that  have killed, but still they are present into db,

SQL> alter system kill session 'sid, serial#';

SQL> select status, username from v$session;

            status    killed
            username  username 

I 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>

After doing some googling i got my answer.

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. 
   
In same case it has to rollback or rook forward for this session data so  until rollback or rook forward is not completed it will show as marked kill.  
 
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.
 



No comments:

Post a Comment