This discussion is archived
6 Replies Latest reply: Jan 8, 2013 12:54 PM by user496899 RSS

Problem dropping a database link (it's in use)

979263 Newbie
Currently Being Moderated
Hi,

I know that this error has been reported in older threads, but I was not able to find the explanation/solution in my context. When I try to drop a database link that I have created (with "DROP DATABASE LINK..."), I get the error "Error SQL: ORA-02018: database link of same name has an open connection". Trying to close the connection with "exec DBMS_SESSION.CLOSE_DATABASE_LINK('database-link-name')" reports the error "ORA-02080: database link is in use", so it doesn't really solve the problem.

The strange thing is that if I execute "SELECT * FROM V$DBLINK" and then I try to drop it again, then dropping the database link works (??). As far as I know, no cursor should be in use for the database link...

Of course, the error with drop only occurs if have used the link (e.g., with a "SELECT * from table-name@database-link-name"), not if for example I create it and then drop it immediately.

Thanks for any idea.
  • 1. Re: Problem dropping a database link (it's in use)
    Vivek L Expert
    Currently Being Moderated
    Read the Follow Up from Tom on similar question
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1253651449550#67593509817769
    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.
  • 2. Re: Problem dropping a database link (it's in use)
    979263 Newbie
    Currently Being Moderated
    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"...
  • 3. Re: Problem dropping a database link (it's in use)
    John Spencer Oracle ACE
    Currently Being Moderated
    user12292809 wrote:
    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"...
    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.

    John
  • 4. Re: Problem dropping a database link (it's in use)
    979263 Newbie
    Currently Being Moderated
    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?).
  • 5. Re: Problem dropping a database link (it's in use)
    John Spencer Oracle ACE
    Currently Being Moderated
    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.

    John
  • 6. Re: Problem dropping a database link (it's in use)
    user496899 Newbie
    Currently Being Moderated
    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?

    TIA,

    dvz

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points