Identifying PID/SPID for killed session in 11g
Many times I killed session with alter system kill session command ( or Toad )
and then session would just hang without dying.Then when I tried to find which OS process is related to killed session I 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.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 I 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
which can help in resolving this issue.