ORA-02049 and ORA-02063 Errors

Posted on

Question :


is performed on a table by 20 processes in parallel

The error below is returned by the 4 processes out of 20 (16 processes are successful)

SQL_UPDATE_ERROR- : SQL update error ORA-02049: timeout: distributed transaction 
waiting for lock ORA-02063

Are there some parameters in Oracle, which define how many connections can be made to a table using a database link?

Order of the Steps is Select

Answer :

It’s not necessarily the number of transactions, but the timeout. The parameter distrubuted_lock_timeout is set to 60 seconds by default. The purpose of this parameter is to avoid having distributed transactions in a long running wait status while something else is performing work on that row; the transaction will wait 60 seconds, then Oracle kills it.

You can modify this parameter (requires an instance restart) to whatever you want (in seconds).

Got a Solution. Increase the Value of INITRANS and Rebuild the Indexes Helped.
Table accessed via DB_LINK has INITRANS = 1, resulting in too much ITL Waits ~200 and Row Lock Waits ~100000.

Now the INITRANS Value is increased to 20, resulting in No Row Lock wait for Distributed/Concurrent Transactions.

Leave a Reply

Your email address will not be published. Required fields are marked *