认真就输

How To Find The Process Identifier (pid, spid) After The Corresponding Session Is Killed? (文档 ID 387077.1)修改时间:2013-3-2类型:PROBLEMIn this Document  Symptoms  Cause  Solution  ReferencesThis document is being delivered to you via Oracle Support’s Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.APPLIES TO:Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 11.1.0.7 – Release: 9.2 to 11.1Information in this document applies to any platform.***Checked for relevance on 25-Jul-2010***SYMPTOMSWhen killing a session with ‘alter system kill session’ the value paddr in v$session changes while the addr corresponding value in v$process does not.As a result, it is no longer possible to identify the process that has been killed and terminate it at OS levelIt is very easy to check (on a solaris 64 bit machine):1. Create a new session2. get the sid:SQL> select distinct sid from v$mystat;SID—1403. check paddr in v$session and addr in v$process (and the spid of the process)SQL> select spid,addr from v$process where addr in (select paddr fromv$session where sid=140);SPID ADDR———— —————-1011 0000000398E5CAA04. kill the sessionSQL> alter system kill session ‘140,9752’;5. check paddr in the v$session and addr in v$process:SQL> select paddr from v$session where sid=140;PADDR———————0000000398E9E3E8SQL> select addr from v$process where spid=1011;ADDR———————0000000398E5CAA0As it can be seen, after killing the session, the paddr changes only in v$session. It is no longer possible to join the 2 views.CAUSEBug 5453737 WHEN A SESSION IS KILLED, PADDR CHANGES IN V$SESSION BUT ADDR NOT IN V$PROCESSclosed as not a bug with the following 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. This is expected. SOLUTIONIt is not possible to identify the killed session process from a direct join between v$process and v$session in releases inferior(低于) to 11g. This problem is addressed in internal BUG:5379252 – Hard To Determine Server Processes Which Owned Killed SessionThe following workaround has been recommended: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)    and addr not in (select paddr from v$shared_server);As a result of the bug, 2 additional columns have been added to V$SESSION from 11g on:V$SESSIONCREATOR_ADDR – state object address of creating processCREATOR_SERIAL# – serial number of creating processCREATOR_ADDR is the column that can be joined with the ADDR column in V$PROCESS to uniquely identify the killed process corresponding to the former session.Following the previous example, this would identify the killed session:select * from v$process where addr=(select creator_addr from v$session where sid=140);Two more views that can be helpful for the subject have been introduced in 11gV$PROCESS_GROUPINDX – IndexNAME – The name of the process group. The default group is called DEFAULT.PID – Oracle process idV$DETACHED_SESSIONINDX – IndexPG_NAME – The process group name that owns this session. The default group is DEFAULT.SID – Oracle session id.SERIAL# – Session serial number.PID – Oracle process id.Unfortunately, these changes are only available in the Oracle releases at least equal to 11.1.0.6 and cannot be backported to previous releases.REFERENCESBUG:5453737 – WHEN A SESSION IS KILLED, PADDR CHANGES IN V$SESSION BUT ADDR NOT IN V$PROCESS