1, to see if there is a lock table sql
Copy Code code as follows:
Select ' Blocker (' | | lb.sid| | ': ' | sb.username| | ') -sql: ' | | Qb.sql_text blockers,
' Waiter (' | | lw.sid| | ': ' | sw.username| | ') -sql: ' | | Qw.sql_text Waiters
From V$lock lb,
V$lock LW,
V$session SB,
v$session SW,
V$sql QB,
V$sql QW
where Lb.sid=sb.sid
and Lw.sid=sw.sid
and sb.prev_sql_addr=qb.address
and sw.sql_address=qw.address
and LB.ID1=LW.ID1
And sw.lockwait is not NULL
and sb.lockwait is null
and lb.block=1;
2, view the locked table
Copy Code code as follows:
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;
3, see that user that process caused deadlock, the level of the lock
Copy Code code as follows:
Select B.owner,b.object_name,l.session_id,l.locked_mode fromv$locked_object L, dba_objects
4, view the process of the connection
Copy Code code as follows:
SELECT SID, Serial#, username, Osuser fromv$session;
5, see which session is caused by
Copy Code code as follows:
Select B.username,b.sid,b.serial#,logon_time
From V$locked_object a,v$session b
where a.session_id = B.sid order by B.logon_time;
6. Kill the process
Copy Code code as follows:
Alter system kill session ' sid,serial# ';
SID is the SID and Serid of step 5th query