联系：手机/微信(+86 17813235971) QQ(107644445)
标题：找出 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)
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.