查看oracle死锁

1、用dba权限的用户查看数据库都有哪些锁

select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

如：testuser 339 13545 2009-3-5 17:40:05
知道被锁的用户testuser，sid为339，serial#为13545

长时间会话：
select l.sid,l.serial#,l.start_time,l.elapsed_seconds,l.time_remaining,l.opname,l.target,t.sql_text
from v$session_longops l,v$sqlarea t
where l.sql_hash_value=t.hash_value
 and l.time_remaining>0
 order by l.time_remaining desc;
等待：
 select sid,seq#,event,state,wait_time from v$session_wait where event='i/o slave wait';
  
  select sql_text from v$session a,v$sqltext_with_newlines b
  where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value
  and a.sid in(select sid from v$session_wait group by sid) order by piece;

2、根据sid查看具体的sql语句，如果sql不重要，可以kill

select sql_text from v$session a,v$sqltext_with_newlines b
  where DECODE(a.sql_hash_value, 0, prev_hash_value, sql_hash_value)=b.hash_value
  and a.sid=&sid order by piece;

查出来的sql，如： begin :id := sys.dbms_transaction.local_transaction_id; end;

如何通过操作系统进程ID查session ID

select sid,serial# from v$session where PROCESS＝‘进程ID’
select ses.sid from v$session ses,v$process pro where pro.spid=&spid and ses.paddr=pro.addr;
其中&spid换成想要查的操作系统进程ID即可，应该可以实现的。

3、kill该事务
 select 'alter system kill session ''' || SID||','||serial# ||''';'
 from v$session where
 username ='XXX';

select 'alter system kill session ''' || t2.sid||','||t2.serial# ||''';' from v$locked_object t1,v$session t2
where t1.session_id=t2.sid and username in ('EEDS_XRAPUSER','EEDS_XRAPSYS','GIAPUSER','GIAPSYS') order by t2.logon_time ;