数据库升级到10g后怪事就是多，经常碰到session被killed，第二天早上来看还是没被后台进程清除。这种情况下就不能用v$process和v$session表关联查出spid,接着在os中杀进程啦。可以用下面的方法处理。[@more@]

select spid, program, a.*
from v$process a
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);

11G中v$session加了CREATOR_ADDR、CREATOR_SERIAL#，找出killed会话的spid更方便啦。

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 PADDR column in V$PROCESS to uniquely identify the killed process corresponding to the former session.

select * from v$process where addr=(select creator_addr from v$session where sid=140);

ITPub博客

kill session后，会话被标识为killed，没有被清除

最新文章