ORA-02049: timeout: distributed transaction waiting for lock

You can get ” ORA-02049: timeout: distributed transaction waiting for lock ” error during the drop any user.

 

ORA-02049: timeout: distributed transaction waiting for lock

 

Details of error are as follows.

ORA-02049: timeout: distributed transaction waiting for lock

Cause: exceeded INIT.ORA distributed_lock_timeout seconds waiting for lock.

Action: treat as a deadlock

ERROR at line 1:

ORA-02049: timeout: distributed transaction waiting for lock

ORA-02063: preceding line from TABLE_NAME

ORA-02049

To solve this error, You can increase the distributed_lock_timeout parameter as follows.

SQL> show parameter distributed_lock_timeout
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
distributed_lock_timeout             integer     60

SQL> alter system set distributed_lock_timeout=600 scope=spfile sid='*';

 

 

timeout: distributed transaction waiting for lock

And Sometimes this error is got because of open_links , so To solve this error, You should increase the open_links parameter as follows.

alter system set open_links=20 scope=spfile sid='*';
alter system set open_links_per_instance=20 scope=spfile sid='*';

Then restart database to activate these parameter as follows.

 

shutdown immediate;
startup;

 

If you don’t want to increase open_links because of restart database, then you can close the existing database links as follows.

 

SQL> exec DBMS_SESSION.CLOSE_DATABASE_LINK (Database_Link_Name);

SQL> alter session close database link Database_Link_Name;

 

 

 

Or you can identify which database links in doubt transactions waiting for manual rollback or commit on Remote database.

SQL> SELECT LOCAL_TRAN_ID, GLOBAL_TRAN_ID, STATE, MIXED, COMMIT# FROM DBA_2PC_PENDING;

132.71.1435859     CORE.0d9f9683.882.14.323447  forced   rollback  no  40514891501

 

You can run manual rollback or commit as follows.

ROLLBACK FORCE '132.71.1435859';


OR


COMMIT FORCE '132.71.1435859';

 

 

 

Do you want to learn Oracle Database for Beginners, then Click and read the following articles.

Oracle Database Tutorials for Beginners ( Junior Oracle DBA )