Oracle Session被kill后如何获取会话的进程号

一.背景说明:

oracle11gR1以前的数据库版本，如果在将session从数据库层面杀死后v$session里面该sid的paddr就发生了变化，这会引起我们无法通过关联v$process来获取spid达到从操作系统层面清理进程的现象。测试步骤如下:

1.创建一个新的session并获取其sid和paddr和process的相关信息:

select a.spid,a.addr,b.paddr,b.sid,b.serial# from v$session b, v$process a where a.addr=b.paddr and b.sid=(select distinct sid from v$mystat);

SPID   ADDR              PADDR             SID  SERIAL#
------ ----------------  ---------------- ---- --------
2514   00000000784C5DF8  00000000784C5DF8   43       21

2.从数据库层面杀死该session：

alter system kill session '43,21';

System altered

3.获取v$session和v$process中对应的addr已经发生变化：

select sid,serial#,paddr from v$session where sid=43;

SID SERIAL# PADDR          

--- ------- ----------------

43      21 000000007854DEB0

select addr,spid from v$process where spid=2514;

ADDR             SPID

---------------- ------------------------

00000000784C5DF8 2514

二.原因分析

从上面的现象可知道，session被kill后paddr会发生变化，这使我们不能通过关联v$process和v$session视图获取对应的spid。metalink官方的说法是因为oracle对进程的回收机制(原来定义为Bug 5453737),原理描述为：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.

三.处理方式

3.1.旧版本

11.1以前版本可以通过以下语句查询到对应的操作系统进程号,但是经过测试后发现可能把v$dispatcher视图中的进程中搜出来，最好就是也排除此视图:

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$dispatcher) 
    and addr not in (select paddr from v$shared_server);

3.2.新版本

11.1以后的版本通过在v$session视图添加了两个新字段来达到屏蔽该问题的目的:

CREATOR_ADDR - state object address of creating process
CREATOR_SERIAL# - serial number of creating process

所以我们可以通过creator_addr与v$process中的addr字段关联后获取对应进程号:

select * from v$process where addr=(select creator_addr from v$session where sid=140);