This content has been marked as final. Show 6 replies
Read the Follow Up from Tom on similar question
The fact is that database links stay open for the duration of the session unless and until you close them...
alter session close database link <linkname>
so, close it.
user12292809 wrote:You also need to do a commit or rollback in the session that used the dblink before you can close it, even if you only used the dblink for a select query. Depending on what tool you are using, you may also need to close or "overwrite" (by running another query) the window that displayed the query from the dblink to close the cursor.
Thanks for the answer.
Yes, I had already looked at that thread and tried to execute "alter session close database link <linkname>" (which I think is equivalent to "exec DBMS_SESSION.CLOSE_DATABASE_LINK('database-link-name')"), but that returns the error "ORA-02080: database link is in use"...
Thanks, it's really strange but it seems that the trick is precisely just to submit another query. Even if the query fails because you ask about a nonexisting table, this will allow you to drop the database link... In this test I was using SQL Developer. I cannot find an explanation for this strange behavior (is SQL Developer keeping some cursor internally?).
I have no idea whether SQL Developer is keeping a cursor open even after a commit or rollback, but I have certainly seen this behaviour using both run statement and run script. As far as I can tell, closing the tab where you got the query results often (but not always) works, running select * from dual in the local instance as run statement or run script as appropriate always works.
Hi John, I follow about the commit/rollback to close a dblink. Two things aren't clear (well, just these two for now ):
-- given a query statement, is there any performance gain from using a rollback instead of the commit? And
-- are you saying that a subsequent and local "select * from dual" will close the open cursors for the calling session?