First find the session that leads to "dead lock"
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
Then Kill dropped the session of the deadlock ALTER SYSTEM KILL Session 'SID, Serial #' (SID, Serial # found from the above query results)
