select sid,serial#,paddr from v$session where sid=463
SID SERIAL# PADDR
463 16436 07000000CF68B308
当kill掉这个session后,paddr会改变,因此不能再使用下面的sql查找spid,再kill -9
select spid,osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=463
如:执行 select * from v$session where sid=463后 ,
select * from v$session where sid=463 查看 paddr=07000000CF76DDD0,已变化
所以,最好在kill session前找到 对应的spid ,方便操作系统上kill -9
不然,使用下面的sql也可以找到
select *
from (select s.username,
s.serial#,
s.sid,
s.status,
x.addr,
x.ksllapsc,
x.ksllapsn,
x.ksllaspo,
x.ksllrtyp,
decode(bitand(x.ksuprflg, 2), 0, null, 1)
from x$ksupr x, v$session s
where s.paddr(+) = x.addr
and bitand(ksspaflg, 1) != 0) a,
(select p.addr
from v$process p
where pid <> 1
minus
select s.paddr from v$session s where s.status = 'KILLED') b
where a.addr = b.addr;
最后一行,没有username,sid,serial#的那条就是,
根据addr 查到spid 即可
select * from v$process where addr='07000000CF68CAA8'