title: ORA-00060: Deadlock Trace file query found but can we get its parameter values
body: <p>As this ORA-00060 is quite famous and I too have analyzed it in past and have solved a few of them in past. In my case I have ensured there is no common update from multiple session, still the error was occurring, and for such solutions I have used to increase the initrans and frequent commit in the concurrent transactions have helped me to solve it.
  Now in another program again I saw the ORA-00060. Then I analyze the .trc file. There it is showing the merge query running from two session which is causing deadlock. This query has bind parameters. I wanted to know the values of this bind parameter. Because if by any bug if this binding parameter is same from the two session then it will be obvious that the two sessions are updating the same records, and eventually causing the deadlock.
  Where I am struggling is that I am not able to see any way to get the value of these bind parameter in the .trc file. The call stack trace in the .trc file does have some blocks but very difficult to figure out where and what is the value of this parameter when the deadlock graph was created. <strong>Does anyone know whether it is possible to get the bind parameter values from .trc file for the queries causing the deadlock?</strong> I have started putting the log in the code so that next time when it occurs I will get that from normal logs.</p>
  <p>Deadlock graph:
  ------------Blocker(s)-----------  ------------Waiter(s)------------</p>
  <p><code>Resource Name                             process session holds waits serial  process session holds waits serial TX-0012000B-0004A991-00000000-00000000        193     203     X         7643     195     583           S  11914 TX-000F000E-000871FC-00000000-00000000        195     583     X        11914     193     203           S   7643 </code></p>
  <p>The query (table/column names I have changed) is:</p>
  <pre><code>----- Information for waiting sessions -----
  Session 203:
  sid: 203 ser: 7643 audsid: 38682755 user: 151/SCHMEA1
  ...
  pid: 193 O/S info: user: grid, term: UNKNOWN, ospid: 11010512
  image: oracle@hostname
  client details:
  ...
  application name: JDBC Thin Client, hash value=2546894660
  current SQL:
  MERGE INTO TABLE_A TA USING ZPD_TABL2 ZPD ON (TA.COY=ZPD.COY AND TA.COL2=ZPD.COL2 AND TA.TRXNO=ZPD.TRXNO AND (TA.COL3 = ' ' OR TA.COL3 IS NULL OR TA.COL3 = 'I') AND ZPD.THREADNO=:B1 ) WHEN MATCHED THEN UPDATE SET TA.COL3 = 'Y', TA.USRPRF=:B3 , TA.JOBNM=:B2 , TA.DATIME=LOCALTIMESTAMP
  
  Session 583:
  sid: 583 ser: 11914 audsid: 38682758 user: 151/VM1DTA
  ...
  pid: 195 O/S info: user: grid, term: UNKNOWN, ospid: 58064926
  image: oracle@hostname
  client details:
  ..
  application name: JDBC Thin Client, hash value=2546894660
  current SQL:
  MERGE INTO TABLE_A TA USING ZPD_TABL2 ZPD ON (TA.COY=ZPD.COY AND TA.COL2=ZPD.COL2 AND TA.TRXNO=ZPD.TRXNO AND (TA.COL3 = ' ' OR TA.COL3 IS NULL OR TA.COL3 = 'I') AND ZPD.THREADNO=:B1 ) WHEN MATCHED THEN UPDATE SET TA.COL3 = 'Y', TA.USRPRF=:B3 , TA.JOBNM=:B2 , TA.DATIME=LOCALTIMESTAMP
  
  ----- End of information for waiting sessions -----
  </code></pre>
  <p>So if I get the value of THREADNO =:B1 (this bind parameter) value from .trc file then I can confirm whether these two merge from two sessions are updating the same records or not.</p>