Oracle deadlock detect script

查看SQL死锁

select 	a.username username, 
		a.sid sid, 
		a.serial# serial,
		b.id1 id1, 
		c.sql_text sqltext 
from 	v$session a, 
		v$lock b, 
		v$sqltext c 
where 	b.id1 in (select distinct e.id1 from v$session d,v$lock e where d.lockwait = e.kaddr)    
		and a.sid = b.sid    
		and c.hash_value = a.sql_hash_value    
		and b.request = 0;

select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Wait' "Status", 
		a.username, 
		a.machine, 
		a.sid, 
		a.serial#, 
		a.last_call_et "Seconds", 
		b.id1, 
		c.sql_text "SQL" 
from 	v$session a, 
		v$lock b, 
		v$sqltext c 
where 	a.username is not null 
and 	a.lockwait = b.kaddr 
and 	c.hash_value =a.sql_hash_value 
union 
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ 'Lock' "Status", 
		a.username, 
		a.machine, 
		a.sid, 
		a.serial#, 
		a.last_call_et "Seconds", 
		b.id1, c.sql_text "SQL"
from 	v$session a, 
		v$lock b, 
		v$sqltext c 
where 	b.id1 in (select /*+ NO_MERGE(d) NO_MERGE(e) */ distinct e.id1 from v$session d, v$lock e where d.lockwait = e.kaddr) 
and 	a.username is not null 
and 	a.sid = b.sid 
and 	b.request=0 
and 	c.hash_value =a.sql_hash_value;

    select p.spid,  
           a.serial#,   
           c.object_name,  
           b.session_id,  
           b.oracle_username,  
           b.os_user_name   
    from   v$process p,v$session a, v$locked_object b,all_objects c   
    where  p.addr=a.paddr and   
           a.process=b.process and   
           c.object_id=b.object_id;