Monday, April 23, 2012

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.

5 comments:

Michael said...

What version of SQL Developer did you use? I exprience the same problem in 3.0.04. This bug has to be reported to Oracle, I guess.

Byte64 said...

I am on 3.1.07.
If you mean to open a TAR, that's out of my reach, I have no support contract currently open.

Thank you.
Flavio

Anonymous said...

Ora 02080 have two possible causes:
a transaction is active or a cursor is open

With the commit trick you solve the first one.
Probably SQL*Developer left a cursor open if you execute something like "select * from table@testlink" and the tool open a grid for show the results.
(I could reproduce this behaviour on SQL*Navigator)

Anonymous said...

I've been facing this error in SQL Developer for some time. Just recently I've found out what did the trick. And it doesn't seem to be a bug in SQL Developer.
As previous Anonymous said - SQL Developer apparently keeps cursors open for select statements based on the dblink which still have open query result tabs (grids).
If you close those tabs, you can then easily close/drop the dblink.

Byte64 said...

Thanks for reporting this.

Flavio

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles