How to Troubleshoot Ora-02049 and Lock Problems in General with Oracle

How to troubleshoot ORA-02049 and lock problems in general with Oracle

One possible way might be to increase the INIT.ORA parameter for distributed_lock_timeout to a larger value. This would then give you a longer time to observe the v$lock table as the locks would last for longer.

To achieve automation of this, you can either

• Run an SQL job every 5-10 seconds that logs the values of v$lock or the query that sandos has given above into a table and then analyze it to see which session was causing the lock.

• Run a STATSPACK or an AWR Report. The sessions that got locked should show up with high elapsed time and hence can be identified.

v$session has 3 more columns blocking_instance, blocking_session, blocking_session_status that can be added to the query above to give a picture of what is getting locked.

A distributed transaction is waiting for lock

Because there are too many row in your Table, When you UPDATE table will be lock.
oracle default it set to 60 seconds. if your excute time over 60 seconds will be error.

You can try to set up timeout value

ALTER SYSTEM SET distributed_lock_timeout=120;

or disable it.

ALTER SYSTEM DISABLE DISTRIBUTED RECOVERY;

https://docs.oracle.com/cd/A84870_01/doc/server.816/a76960/ds_txnma.htm

Note:

• Remember : While running any ALTER SYSTEM Command you need to restart the instance.

Related Topics