How do I close the cursor created in a database link(ORA-02080)?

Question

I perform the following sequence of steps(step-wise using F-9) in Oracle SQL Developer:

create database link mydb connect to my_schema identified by mypwd using 'connection1';

select * from users where id = 1;

rollback;
ALTER SESSION CLOSE DATABASE LINK mydb;
drop database link mydb;

I get an error when I try to close the database link:

SQL Error: ORA-02080: database link is in use 02080. 00000 - "database link is in use"

*Cause: a transaction is active or a cursor is open on the database link given in the alter session close database link command.

*Action: commit or rollback, and close all cursors

The transaction is finished, but when I do select * from V$DBLINK; I see there is still an open cursor, but I have no idea how I can close that one.

Which version(s) of the database is this happening in? For the source and target databases, i.e. either end of the link? I can't immediately reproduce, or see how to start a transaction from a simple query. — Alex Poole
Where and how are you running this? I can generate this in SQL Developer if I run the remote query as a statement so the result goes into a grid (and there is more than a page of data, which keeps the cursor open), and everything else as a script from the worksheet. How many rows should your query produce, and how many do you fetch before you try to close the link? You need to close the result set before closing the link; the reference to a transaction is a bit misleading here. — Alex Poole
And exec DBMS_SESSION.CLOSE_DATABASE_LINK('MYDB'); gets the same ORA-02080 error in that scenario, incidentally. — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2015-05-26T15:07:25

The cursor is held open as long as the SQL Developer "Query Results" tab is open, even if you scroll to the end of the result set. If you close that tab the cursor will be released and you'll be able to close and drop the database link.

Or if you have the "show query results in new tabs" box unchecked in the preferences, under Database->Worksheet, you could run a different query which doesn't reference the link as a statement, which would reuse the same results tab and close the previous cursor. If that box is checked then that won't help, of course.

You don't really need the separate close step if you're dropping it anyway; and if you just drop it without closing you don't need to roll back or even close the result set.

If you ran all of your commands as a script (F5), rather than as individual statements, you wouldn't see the error; but your data would be displayed in the "Script Output" window with everything else, not in the "Query Results" data grid.

How do I close the cursor created in a database link(ORA-02080)?

1 Answers