Tuesday, December 1, 2015

How to Identify Deadlock Object

You can get trace file from the alert.log

Example trace file 

Deadlock graph:
                                                       ---------Blocker(s)--------           ---------Waiter(s)---------
Resource Name                        process  session   holds waits  process session   holds waits
TX-00450014-00012495                 78    1213     X                       216         92      X
TX-0044000b-00013342               216        92     X                         78     1213       X


Rows waited on:
  Session 1213: obj - rowid = 0005DD13 - AABd0TAAXAAARF5AAE
  (dictionary objn - 384275, file - 23, block - 70009, slot - 4)
  Session 92: obj - rowid = 0005DD5F - AABd1fAAJAAAMlRABP
  (dictionary objn - 384351, file - 9, block - 51537, slot - 79)



SELECT owner, object_name, object_type 
FROM dba_objects 
WHERE object_id = 384275;

SELECT * 
FROM owner.object name  <---- from above query
WHERE rowid = 'AABd0TAAXAAARF5AAE';