Identifying PID/SPID for killed session in 11g

Many times when we kill a session with alter system kill session command  ( or Toad )

and then session would just hang without dying. Then when we tried to find which OS process is related to killed session and we could not identify it directly by using v$session and v$process view and joining them using addr column. According to Oracle this is expected. And here is explanation:

When a session is killed, the session state object(and all the child state objects under the session state object) move out from under the original parent process state object, and are placed under the pseudo process state object (which is expected, given the parent/child process mechanism on Unix). PMON will clean up all the state objects found under the pseudo process state object. That explains why PADDR changes in V$SESSION when a session is killed. New PADDR you are seeing in v$SESSION is the address of the pseudo process state object. This shows up in system state under PSEUDO PROCESS for group DEFAULT: V$PROCESS still maintains the record of the original parent process.

So to finally get rid of that session we  had to use different workarounds. One of the workaround was to eliminate PSEUDO processes.

select spid, program

from v$process where program != 'PSEUDO'

and addr not in

(select paddr from v$session);    and of course avoid killing background processes

select spid, program from v$process

where program!= 'PSEUDO'

and addr not in (select paddr from v$session)

and addr not in (select paddr from v$bgprocess);

Thanks to 11g  there are two new columns in v$session view :

CREATOR_ADDR – state object address of creating process

CREATOR_SERIAL# – serial number of creating process

CREATOR_ADDR is the column that can be joined with the PADDR column in V$PROCESS to uniquely identify the killed process corresponding to the former session.

So new query would look like:

select * from v$process where addr=(select creator_addr from v$session 

where sid=< sid used in alter system kill session command >);

In addition to these columns there are views

V$DETACHED_SESSION

V$PROCESS_GROUP

which can help in resolving this issue.