链接：http://www.xifenfei.com/3197.html
标题：找出 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)
|
扫一扫
1804
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
