在Session被kill后，如何查找进程标识符(pid, spid) (Doc ID 387077.1)

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

APPLIES TO:

Oracle Database - Enterprise Edition - Version 9.2.0.1 to 12.2.0.1 [Release 9.2 to 12.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 04-Feb-2016***
 

SYMPTOMS

When 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.  当使用'alter system kill session' 终止会话时，v$session中的paddr值会更改，而v$process中的addr对应的值不会更改。
As a result, it is no longer possible to identify the process that has been killed and terminate it at OS level
结果，不再可能确定已终止的进程并在OS级别终止它
It is very easy to check (on a solaris 64 bit machine):  检查非常容易（在solaris 64位计算机上）
1. Create a new session  创建一个新的会话
2. get the sid:   得到sid

SQL> select distinct sid from v$mystat;
SID
---
140

3. check paddr in v$session and addr in v$process (and the spid of the process)  检查v$session中的paddr和v$process中的addr（以及进程的spid）

SQL> select spid,addr from v$process where addr in (select paddr from
v$session where sid=140);

SPID ADDR
------------ ----------------
1011 0000000398E5CAA0

4. kill the session

SQL> select sid,serial# from v$session where SID='140';

       SID    SERIAL#
---------- ----------
       140       9752

SQL> alter system kill session '140,9752';

5. check paddr in the v$session and addr in v$process:  检查v$session中的paddr和v$process中的addr：

SQL> select paddr from v$session where sid=140;

PADDR
---------------------
0000000398E9E3E8

SQL> select addr from v$process where spid=1011;

ADDR
---------------------
0000000398E5CAA0

As 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.
可以看出，终止会话后，paddr仅在v$session中更改。 无法再加入这两个视图。

CAUSE

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:  通过以下解释将其关闭为非bug
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.

当会话被杀死时，会话状态对象（以及会话状态对象下的所有子状态对象）将从原始父流程状态对象下移出，并置于伪流程状态对象下（在给定 Unix上的父/子进程机制）。 PMON将清除在伪进程状态对象下找到的所有状态对象。 这就解释了为什么在终止会话时PADDR在V$SESSION中会发生变化。 在v$SESSION中看到的新PADDR是伪进程状态对象的地址。 这在组DEFAULT的PSEUDO PROCESS下的系统状态中显示：V$PROCESS仍保留原始父进程的记录。 这是预期的。

SOLUTION

It 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  在低于11g的发行版中，无法通过v$process和v$session之间的直接联接来确定终止的会话进程。 此问题已在内部解决
BUG:5379252 - Hard To Determine Server Processes Which Owned Killed Session

The 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:  由于该错误，从11g开始， V$SESSION已添加2个附加列：
V$SESSION
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 ADDR column in V$PROCESS to uniquely identify the killed process corresponding to the former session.  是可以与V$PROCESS中的ADDR列连接的列，以唯一地标识与前一个会话相对应的终止进程
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 11g  11g中引入了另外两个对主题有帮助的观点
V$PROCESS_GROUP
INDX - Index    索引
NAME - The name of the process group. The default group is called DEFAULT.进程组的名称。 默认组称为DEFAULT
PID - Oracle process id    Oracle进程ID

V$DETACHED_SESSION
INDX - Index  索引
PG_NAME - The process group name that owns this session. The default group is DEFAULT. 拥有此会话的进程组名称。 默认组为DEFAULT
SID - Oracle session id.  Oracle会话ID
SERIAL# - Session serial number.  会话序列号
PID - Oracle process id.  Oracle进程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.  不幸的是，这些更改仅在至少等于11.1.0.6的Oracle版本中可用，而不能反向移植到以前的版本中。

REFERENCES

BUG:5453737 - WHEN A SESSION IS KILLED, PADDR CHANGES IN V$SESSION BUT ADDR NOT IN V$PROCESS