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.

Answer 1

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.