ORA-02080: database link is in use

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Yet another web article about ORA-02080? Hopefully not.

ORA-02080: database link is in use

After reading several entries on this error and its remedies, including Tom Kyte's entry, I realized that the suggested cure is not always working, at least not on Oracle XE 10gR2 (at the moment I can't say on other versions, may be you can contribute on this).

Say we a database link called TEST_LINK.
The theory says that in order to explicitly close a database link connection, you must perform either of the following:

COMMIT; -- or ROLLBACK;

ALTER SESSION CLOSE DATABASE LINK test_link;

or

COMMIT; -- or ROLLBACK;

begin dbms_session.close_database_link('TEST_LINK'); end;

I also tried with ROLLBACK FORCE to no avail.
However if, after issuing either of the above, you are still getting the dreaded error message:

ORA-02080: database link is in use

it means it's time to ask what type of client you are issuing the SQL statements from.

I found out that when using SQL Developer, it's more likely to happen, even though in a bunch of cases the closing statement worked, apparently a few minutes after the COMMIT, on the contrary, SQL*Plus never failed a shot.
This odd behavior seems to be confirmed by running the following query as sysdba:

select * from v$open_cursor
where user_name = 'TEST'
and sql_text like '%test_link%';

In the case of SQL*Developer I still find the statement in V$OPEN_CURSOR, regardless of the transaction state (committed or rolled back), in the case of SQL*Plus is never listed there.

So here we have a situation where SQL*Developer exhibits a somewhat erratic behavior when it comes to closing a database link, whereas SQL*Plus seems to be always consistent.

Now, the truth is that, in most cases, you just don't need to care about this, unless, for some reason, you need to drop the database link immediately after executing the distributed query, in which case you can disconnect the session and retry the operation and so far I never encountered problems.

In conclusion I can't say why SQL Developer and SQL*Plus give different results when it comes to closing a database link, if you know something more about this, please chime in.

See message translations for ORA-02080 and search additional resources.