• From: "Remigiusz Sokolowski" <remigiusz.sokolowski@xxxxxxxxxx>
  • To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 25 Aug 2010 13:51:10 +0200

Hi list,

From time to time in some Tuxedo-based app we hit an ORA-2049. The main
problem with it (at least for me) is that this is hard target for any

When hitting it we have the following state on a database (v.
on Solaris) - there is no entries in v$lock which look relevant, however
a table on which we try a dml operation is pinned - so it is easy to
find that this table takes a part in some operation, but I can not find
any trail, which session keeps this lock.
I found only two somewhat relevant database settings:
1 - distributed_lock_timeout, which defaults to 60sec (and we hit it) -
possible solution is to set it higher (and I may try but this is guessing)
2 - now hidden (not sure since which version) parameter
_distributed_recovery_connection_hold_time, which defaults to 200sec

My impression is that assuming some rows in the processed table take a
part in some distributed transation that failed it is possible that an
another distributed transaction on the same set of rows (or a part of
them) will fail if launched before
since the previous transaction.

My question is twofold:
1 - is the senario presented above reasonable or I just missed some parts?
2 - are there any clever methods for spotting what is going on with such
transactions (assuming dba_2pc_pending empty) in general and which
session holds the lock in particular?

Best regards

Pole nakazi

Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
pos   : DBA at DIiUSI
addr  : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland
phone : +48 58 667 17 43
mobile: +48 602 42 42 77

Nordea Bank Polska S.A. z siedzib? w Gdyni, ul. Kielecka 2, 81-303 Gdynia, 
wpisan? do Rejestru Przedsi?biorców Krajowego Rejestru S?dowego pod numerem: 
dla której dokumentacj? przechowuje S?d Rejonowy Gda?sk - Pó?noc w Gda?sku, 
VIII Wydzia? Gospodarczy Krajowego Rejestru S?dowego, 
o kapitale zak?adowym i wp?aconym w wysoko?ci: 227.593.500,00 z?otych, 
NIP: 586-000-78-20, REGON: 190024711

Other related posts: