Ujang, thanks for confirming that the deadlock trace files are different between non-RAC and RAC. I can't change it to non-RAC since it is production. So, any other ideas how to get the bind variables? Regards, Vlado Barun, M.Sc. Sr. Manager, Database Engineering and Operations Jewelry Television Mobile: 865 335 7652 Email: vlado.barun@xxxxxxx -----Original Message----- From: Ujang Jaenudin [mailto:ujang.jaenudin@xxxxxxxxx] Sent: Tuesday, March 03, 2009 1:24 AM To: Barun, Vlado Cc: oracle-l@xxxxxxxxxxxxx Subject: Re: determining bind values in deadlock situations vlado, I was have the same problem on RAC 10.2.0.3 and yes there is no rowid information in the trace file. due to the dev environment, I have changed it to single instance and got the row. -- thanks and regards ujang | oracle dba | mysql dba http://ora62.wordpress.com On Tue, Mar 3, 2009 at 9:21 AM, Barun, Vlado <Vlado.Barun@xxxxxxx> wrote: > Jared, > > > > Was your test done in a non-RAC environment? > > I can find the rowid's the way you mentioned in a non-RAC environment, but > not in a RAC environment. > > > > There is no trace file generated for a deadlock in udump on any of my RAC > nodes. > > > > Are you able to get a deadlock trace file in a RAC environment? > > > > Thank you for the time you are spending on this. > > > > Regards, > > > > Vlado Barun, M.Sc. > > Sr. Manager, Database Engineering and Operations > > Jewelry Television > > Mobile: 865 335 7652 > > Email: vlado.barun@xxxxxxx > > > > From: Jared Still [mailto:jkstill@xxxxxxxxx] > Sent: Monday, March 02, 2009 1:28 PM > To: Barun, Vlado > Cc: oracle-l@xxxxxxxxxxxxx > Subject: Re: determining bind values in deadlock situations > > > > > > On Fri, Feb 27, 2009 at 5:54 PM, Barun, Vlado <Vlado.Barun@xxxxxxx> wrote: > > Jared, > > However I have trouble finding the rowid's. > > Following are the relevant excerpts of the lmd0 trace files from both nodes. > Where are the rowid's? Am I looking in the correct files? > > > > The deadlock trace file will be found in udump. > > Here's an example from 10.2.0.2 on Windows > > DEADLOCK DETECTED > [Transaction Deadlock] > Current SQL statement for this session: > SELECT "SQLX" , "EDTX" , "DBNA" , "CLAS" , "TYPE" , "OCCURS" , "SUBC" , > "APPL" , "SECU" , "CNAM" , "CDAT" , "VERN" , "LEVL" , "RSTAT" , "RMAND" , > "RLOAD" , "UNAM" , "UDAT" , "UTIME" , "DATALG" , "VARCL" , "DBAPL" , "FIXPT" > , "SSET" , "SDATE" , "STIME" , "IDATE" , "ITIME" , "LDBNAME" , "UCCHECK" , > "MAXLINELN" FROM "REPOSRC" WHERE "PROGNAME" = :A0 AND "R3STATE" = :A1 FOR > UPDATE > The following deadlock is not an ORACLE error. It is a > deadlock due to user error in the design of an application > or from issuing incorrect ad-hoc SQL. The following > information may aid in determining the deadlock: > Deadlock graph: > ---------Blocker(s)-------- > ---------Waiter(s)--------- > Resource Name process session holds waits process session holds > waits > TX-000a002f-00039963 55 367 X 48 > 358 X > TX-00030005-00007864 48 358 X 55 > 367 X > session 367: DID 0001-0037-00000004 session 358: DID 0001-0030-00000004 > session 358: DID 0001-0030-00000004 session 367: DID 0001-0037-00000004 > Rows waited on: > Session 358: obj - rowid = 00016AC6 - AAAWrGAAbAAGT9VAAE > (dictionary objn - 92870, file - 27, block - 1654613, slot - 4) > Session 367: obj - rowid = 00016AC6 - AAAWrGAAbAAGT9VAAD > (dictionary objn - 92870, file - 27, block - 1654613, slot - 3) > Information on the OTHER waiting sessions: > Session 358: > pid=48 serial=17 audsid=1283076 user: NN/SAPR3 > O/S info: user: SapServiceXXX, term: MACHINE , ospid: NNN:NNNN, machine: > domain\machine > program: disp+work.EXE > client info: 0 > application name:XXXXXXXX , hash > value=888649399 > action name: 389, hash value=1894668767 > Current SQL Statement: > > SELECT "SQLX" , "EDTX" , "DBNA" , "CLAS" , "TYPE" , "OCCURS" , "SUBC" , > "APPL" , "SECU" , "CNAM" , "CDAT" , "VERN" , "LEVL" , "RSTAT" , "RMAND" , > "RLOAD" , "UNAM" , "UDAT" , "UTIME" , "DATALG" , "VARCL" , "DBAPL" , "FIXPT" > , "SSET" , "SDATE" , "STIME" , "IDATE" , "ITIME" , "LDBNAME" , "UCCHECK" , > "MAXLINELN" FROM "REPOSRC" WHERE "PROGNAME" = :A0 AND "R3STATE" = :A1 FOR > UPDATE > End of information on OTHER waiting sessions. > > > Jared -- //www.freelists.org/webpage/oracle-l