『管理调优』kill session的相关视图

1 如何查找死锁？

You may have a locking issue. Run a query against dba_ddl_locks such as:

col username for a12
col object_locked for a40
col MACHINE/PID for a30
col program for a30
set linesize 132
set verify off
accept objowner prompt 'Object Owner: '
accept objname prompt 'Object Name : '

select s.username, s.sid, s.program, d.owner || '.' || d.name object_locked,
decode(s.process, NULL, s.machine, s.machine || ' PID: ' || to_char(s.process)) "MACHINE/PID"
from v$session s, dba_ddl_locks d
where s.sid = d.session_id
and d.type like '%/Procedure/%'
and d.owner = upper('&objowner')
and d.name = upper('&objname')

2  想要判断出那些process为已经被kill掉可以通过视图x$ksupr

SELECT s.username,s.status,
    x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,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;

参考网络文章：

  1).   Oracle HowTo:如何快速杀死占用过多资源(CPU,内存)的数据库进程http://www.eygle.com/archives/2005/10/oracle_howto_kill_session.html

  2).  kill session and process

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:4974573906087