Skip to content

How to Resolve ORA-02080: database link is in use

  • by

ORA-02080

ORA-02080 means that the open database link you want to close is in use, you have to close the open transaction in a distributed system first. Let's see an example.

SQL> select sysdate from dual@dblink04;

SYSDATE
-------------------
2017-10-10 19:58:39

When we tried to close the db link by ALTER SESSION or DBMS_SESSION.CLOSE_DATABASE_LINK, we saw ORA-02080.

SQL> alter session close database link dblink04;
ERROR:
ORA-02080: database link is in use
SQL> exec dbms_session.close_database_link ('DBLINK04');
BEGIN dbms_session.close_database_link ('DBLINK04'); END;

*
ERROR at line 1:
ORA-02080: database link is in use
ORA-06512: at "SYS.DBMS_SESSION", line 210
ORA-06512: at line 1

Solution

As we can see, we can't close db link by this way. In a distributed system, we have to explicitly close the open transaction by issuing a COMMIT or ROLLBACK before closing the db link.

In this case, I'd like to leave everything unchanged.

SQL> rollback;

Rollback complete.

Now we can close the db link.

SQL> alter session close database link dblink04;

Session altered.

For more about database links, I have talked about it at: DB Link, How and Why.

Leave a Reply

Your email address will not be published.