You can get ” ORA-02080: database link is in use ” error during the drop any user.
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
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='*';
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 read the following articles.
625 views last month, 2 views today