联系:手机/微信(+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?