找出 alter system kill session ‘sid,serial#’ kill 掉的数据库会话对应进程

发表于 2012 年 5 月 22 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：找出 alter system kill session ‘sid,serial#’ kill 掉的数据库会话对应进程

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

当我们使用alter system kill session ‘sid,serial#’ 在数据库中kill掉某个会话的时候,如果你观察仔细会发现v$session.paddr发生了改变,从而是的不能直接通过关联v$process.add找出spid,然后进行其他操作.本文提供三种方法找该种情况下spid的方法.
数据库版本

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

会话1

SQL> select sid, SERIAL#,paddr from v$session where 
  2  sid=(select sid from v$mystat where rownum=1);

       SID    SERIAL# PADDR
---------- ---------- --------
       133         53 35FE16F4

会话2

SQL> select sid, SERIAL#,paddr from v$session where 
  2  sid=(select sid from v$mystat where rownum=1);

       SID    SERIAL# PADDR
---------- ---------- --------
       143         21 35FE2D3C

会话3

SQL> alter system kill session '133,53';

System altered.

SQL> alter system kill session '143,21';

System altered.

SQL> select sid, SERIAL#,paddr,status from v$session where sid in(133,143);

       SID    SERIAL# PADDR    STATUS
---------- ---------- -------- ----------------
       133         53 3547A3F4 KILLED
       143         21 3547A3F4 KILLED

证明alter system kill session后,v$session中的paddr发生了改变,这个时候如果需要找出原来的spid,不能使用v$session.paddr和v$process.addr关联获得

找出kill掉的spid方法1

SQL> select spid, program from v$process 
  2      where program!= 'PSEUDO' 
  3      and addr not in (select paddr from v$session)
  4      and addr not in (select paddr from v$bgprocess)
  5      and addr not in (select paddr from v$shared_server);

SPID                                             PROGRAM
------------------------------------------------ ------------------------------
14260                                            oracle@xifenfei (L001)
14256                                            oracle@xifenfei (L000)
15300                                            oracle@xifenfei (TNS V1-V3)
14179                                            oracle@xifenfei (D000)
15318                                            oracle@xifenfei (TNS V1-V3)
14252                                            oracle@xifenfei (N000)


SQL> !ps -ef|grep 15300|grep -v grep
oracle   15300 14052  0 03:22 ?        00:00:00 oracleora11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

SQL> !ps -ef|grep 15318|grep -v grep
oracle   15318 15315  0 03:22 ?        00:00:00 oracleora11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

找出kill 掉的spid 方法2

SQL> SELECT s.username,s.status,
  2  x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP,
  3  decode(bitand (x.ksuprflg,2),0,null,1)
  4  FROM x$ksupr x,v$session s
  5  WHERE s.paddr(+)=x.addr
  6  and bitand(ksspaflg,1)!=0;

USERNAME   STATUS           ADDR       KSLLAPSC   KSLLAPSN KSLLASPO                   KSLLID1R KSLL DE
---------- ---------------- -------- ---------- ---------- ------------------------ ---------- ---- --
           ACTIVE           35FD5990          6         26 14121                             0      1
           ACTIVE           35FD6FD8          1         69 14055                             0      1
           ACTIVE           35FD8620          1         69 14055                             0      1
           ACTIVE           35FD9C68          1         69 14055                             0      1
           ACTIVE           35FDB2B0          8         27 15300                             0      1
           ACTIVE           35FDC8F8         12         36 15300                             0      1
           ACTIVE           35FDDF40          1         69 14055                             0      1
           ACTIVE           35FDF588          1         69 14055                             0      1
           ACTIVE           35FE3860          7         26 14236                             0      1
           ACTIVE           35FE4EA8          1         69 14224                             0      1
           ACTIVE           35FE64F0         63          2 14311                           377 EV   1
           ACTIVE           35FEA7C8          3         26 14155                           258 EV   1
           ACTIVE           35FE9180         59          2 14248                           378 EV   1
           ACTIVE           35FE9CA4         12          2 14603                             0      1
           ACTIVE           35FD64B4          1         69 14055                             0      1
           ACTIVE           35FD7AFC          2         27 14055                             0      1
           ACTIVE           35FD9144          2         27 15300                             0      1
           ACTIVE           35FDA78C          3         26 14171                             0      1
           ACTIVE           35FDBDD4         17          2 15255                             0      1
           ACTIVE           35FDD41C         22         26 14155                             0      1
           ACTIVE           35FDEA64         52         26 14155                             0      1
           ACTIVE           35FE4384          1         69 14224                             0      1
           ACTIVE           35FE59CC          1         69 14224                             0      1
           ACTIVE           35FEB2EC          2          2 14248                             0      1
           ACTIVE           35FEC934         11         26 14121                             0      1
SYS        ACTIVE           35FEF5C4          4         16 14117                             0
                            35FE0BD0          1         69 14055                             0
                            35FE865C          1         69 14117                             0
                            35FE7B38          1         69 14117                             0
                            35FE16F4          1         26 14155                             0
                            35FD4E6C          0          0                                   0
                            35FE00AC          2        279 14117                             0
                            35FE2D3C          0          0                                   0
                            35FE7014          2        335 14117                             0
--挑选username和status为null的会话

SQL> select spid,program from v$process where addr in (
  2  '35FE0BD0',
  3  '35FE865C',
  4  '35FE7B38',
  5  '35FE16F4',
  6  '35FD4E6C',
  7  '35FE00AC',
  8  '35FE2D3C',
  9  '35FE7014'
 10  );

SPID                                             PROGRAM
------------------------------------------------ ------------------------------
                                                 PSEUDO
14179                                            oracle@xifenfei (D000)
14183                                            oracle@xifenfei (S000)
15300                                            oracle@xifenfei (TNS V1-V3)
15318                                            oracle@xifenfei (TNS V1-V3)
14252                                            oracle@xifenfei (N000)
14256                                            oracle@xifenfei (L000)
14260                                            oracle@xifenfei (L001)

8 rows selected.
--同样可以发现spid 15300和15318的进程已经在数据库中被kill掉

找出kill掉的spid方法3(11g特有)

SQL> select  spid,program  from v$process where addr in
  2  (select creator_addr from v$session where sid in(133,143));

SPID                                             PROGRAM
------------------------------------------------ ------------------------------
15300                                            oracle@xifenfei (TNS V1-V3)
15318                                            oracle@xifenfei (TNS V1-V3)

找出kill掉的spid方法4(11g特有)

SQL> select * from V$DETACHED_SESSION;

      INDX PG_NAME                                                             SID    SERIAL#        PID
---------- ------------------------------------------------------------ ---------- ---------- ----------
         0 DEFAULT                                                             143         21         21
         1 DEFAULT                                                             133         53         19

SQL> select spid,program from v$process where pid in(21,19);

SPID                                             PROGRAM
------------------------------------------------ ------------------------------
15300                                            oracle@xifenfei (TNS V1-V3)
15318                                            oracle@xifenfei (TNS V1-V3)
此条目发表在 Oracle 分类目录。将固定链接加入收藏夹。

《找出 alter system kill session ‘sid,serial#’ kill 掉的数据库会话对应进程》有 1 条评论

  1. 惜分飞 说：
    2012 年 5 月 22 日19:56

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

    Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.7 - Release: 9.2 to 11.1
Information in this document applies to any platform.
***Checked for relevance on 25-Jul-2010***

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.As a result, it is no longer 
possible to identify the process that has been killed and terminate it at OS level

It is very easy to check (on a solaris 64 bit machine):
1. Create a new session
2. get the 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)
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> 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
---------------------
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.

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: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. 

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 
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:
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.
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
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.

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.