Thursday, July 19, 2012

Finding OS process ID for a killed session

If as database session gets killed, it's value of the PADDR column in V$SESSION cannot be matched with the ADDR from V$PROCESS to find the corresponding process ID ( given that you are using dedicated server). The stateobject of the session is moved to a different parent process so the memory address is change to reflect that. This means the original process is still around but cannot be found joining the V$SESSION and V$PROCESS because the sessions stateobject has been moved to a pseudo process and so the PADDR has been changed.
It is therefore no longer possible to identify the process that has been killed and terminate it at OS level. 

Under some circumstances if a session gets killed, the session is not getting cleaned up immediately but keeps showing up in V$SESSION. There can be several reasons for this: rollback operations etc.

Example for killing a session via sqlplus (issued as SYS ) :

ALTER SYSTEM KILL SESSION '<SID>,<SERIAL#>' ;

<SID> and <SERIAL#> in the above example are taken from the columns SID, SERAL# from the V$SESSION view.

Example killing a session with SID=28 and SERIAL=33455

ALTER SYSTEM KILL SESSION '28,33455' IMMEDIATE;

To find processes which have no matching PADDR in V$SESSION you can issue this query to find the corresponding OS process IDs:

select 
  * 
from 
  v$process 
where 
  addr not in (select paddr from v$session) 
  and addr not in (select paddr from v$shared_server) 
  and addr not in (select paddr from v$bgprocess) 
  and program!= 'PSEUDO';

No comments:

Post a Comment