How To Find The Process Identifier (pid, spid)

How To Find The Process Identifier (pid, spid) After The Corresponding Session Is Killed?

使用alter system kill session ‘sid,serial#’命令杀会话的时候，其session只是被标记killed而已，而且还可以观察到v$session.paddr变化掉，而对应的v$process.addr却没有相应的更改，这就导致，在session被kill之后很难再由这两个列的联系找出操作系统的peocess id。如下
session 1：

SQL> select distinct sid from v$mystat;

       SID
----------
       134

session 2：

SQL> select p.spid, s.username, s.sid, s.serial#, s.status, p.addr, s.paddr
  2    from v$process p, v$session s
  3   where p.addr = s.paddr
  4     and s.sid = 134;

SPID                     USERNAME          SID    SERIAL# STATUS           ADDR             PADDR
------------------------ ---------- ---------- ---------- ---------------- ---------------- ---------------- 19856 SYSTEM 134 36001 INACTIVE 000000007FE5E4C8 000000007FE5E4C8 SQL> alter system kill session '134,36001';

System altered.

SQL> select p.spid,p.addr from v$process p where p.spid=19856;

SPID                     ADDR
------------------------ ---------------- 19856 000000007FE5E4C8 SQL> select s.username, s.sid, s.serial#, s.status,s.paddr from v$session s where s.sid=134;

USERNAME          SID    SERIAL# STATUS           PADDR
---------- ---------- ---------- ---------------- ----------------
SYSTEM 134 36001 KILLED 000000007FEA05F8 SQL> select p.spid, s.username, s.sid, s.serial#, s.status, p.addr, s.paddr
  2    from v$process p, v$session s
  3   where p.addr = s.paddr
  4     and s.sid = 134;

no rows selected

oracle对此的一开始以Bug解释，Bug 5453737 WHEN A SESSION IS KILLED, PADDR CHANGES IN V$SESSION BUT ADDR NOT IN V$PROCESS。不过后来这个Bug并不被当作bug给关闭了，给出的解释是

Bug 5453737 WHEN A SESSION IS KILLED, PADDR CHANGES IN V$SESSION BUT ADDR NOT IN V$PROCESS
closed 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.

但是这个找出标记为killed的session对应的操作系统进程就颇有难度了，尤其是11g之前的版本，这时oracle又给出了一个Bug，BUG:5379252 – Hard To Determine Server Processes Which Owned Killed Session。

为何说11g版本之前更难呢？11g中提供诸多的方便可以解决这个问题。
1、v$session多了两个字段CREATOR_ADDR和CREATOR_SERIAL#
CREATOR_ADDR – state object address of creating process
CREATOR_SERIAL# – serial number of creating process
CREATOR_ADDR可以与v$process.addr进行join操作。

select p.spid, s.username, s.sid, s.serial#, s.status, p.addr, s.paddr
  from v$process p, v$session s
 where p.addr = s.creator_addr
   and s.sid = &sid;

如11g中的输出示例：

SQL> select p.spid, s.username, s.sid, s.serial#, s.status, p.addr, s.paddr
  2    from v$process p, v$session s
  3   where p.addr = s.creator_addr
  4     and s.sid = 140;

SPID  USERNAME          SID    SERIAL# STATUS           ADDR             PADDR
----- ---------- ---------- ---------- ---------------- ---------------- ----------------
413   SYSTEM            140       1911 KILLED           00000000774A0BE0 000000007752DCF8

2、另外就是11g中提供的视图

V$PROCESS_GROUP
INDX – Index
NAME – The name of the process group. The default group is called DEFAULT.
PID – Oracle process id

V$DETACHED_SESSION
INDX – Index
PG_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.

select p.spid, s.username, s.sid, s.serial#, s.status, p.addr, s.paddr
  from v$detached_session d
  join v$session s
    on d.sid = s.sid
   and d.serial# = s.serial#
  join v$process p
    on d.pid = p.pid;

如11g中的输出示例：

SQL> select p.spid, s.username, s.sid, s.serial#, s.status, p.addr, s.paddr
  2    from v$detached_session d
  3    join v$session s
  4      on d.sid = s.sid
  5     and d.serial# = s.serial#
  6    join v$process p
  7      on d.pid = p.pid;

SPID  USERNAME          SID    SERIAL# STATUS           ADDR             PADDR
----- ---------- ---------- ---------- ---------------- ---------------- ----------------
413   SYSTEM            140       1911 KILLED           00000000774A0BE0 000000007752DCF8

比较遗憾的是，这些字段和视图是11g中出现的，从11.1.0.6开始。但是，对于该版本之前的也不是没有办法，但是可以考虑如下的语句：

SELECT addr, pid, spid, program
  FROM v$process
 WHERE NOT EXISTS (SELECT 1 FROM v$session WHERE paddr = addr);

如10g的中输出示例，也就是本文开头的案例:

SQL> SELECT addr, pid, spid, program
  2    FROM v$process
  3   WHERE NOT EXISTS (SELECT 1 FROM v$session WHERE paddr = addr)
  4  /

ADDR                    PID SPID                     PROGRAM
---------------- ---------- ------------------------ -----------------
000000007FE55E60          1                          PSEUDO
000000007FE5BD40         13 19481                    oracle@ace (D000)
000000007FE5C528         14 19483                    oracle@ace (S000)
000000007FE5E4C8         18 19856 oracleSYK@ace

但是需要注意的是，program为PSEUDO，D000和S000千万别被轻易kill掉，kill之前最好做好确认工作。