I have 2 database links that have the same db link name, but different service name. As sys dba, I try to drop the one with the wrong TNS info, but I keep getting the "database link not found," even though I clearly see it in dba_db_links. It's also newly created, so there are no open connections (checked v$dblink and no records returned for anything actually). I tried dropping it as the owner, and same error. I tried dropping it as owner.db_link, still same error. I flushed the shared_pool, same thing. Any other suggestions?

I have 2 database links that have the same db link name, but different service name. As sys dba, I try to drop the one with the wrong TNS info, but I keep getting the "database link not found," even though I clearly see it in dba_db_links. It's also newly created, so there are no open connections (checked v$dblink and no records returned for anything actually). I tried dropping it as the owner, and same error. I tried dropping it as owner.db_link, still same error. I flushed the shared_pool, same thing. Any other suggestions?

• oracle-database
• database-administration
• database-link

Share Improve this question Follow edited Feb 20 at 17:57 Alex Poole 192k1111 gold badges195195 silver badges340340 bronze badges asked Feb 20 at 16:33 BFFBFF 39611 gold badge66 silver badges1717 bronze badges 11

• 2 This is probably off-topic for this site, even if you included the data from dba_db_links and the actual commands and errors you get. I suspect it was created with a quoted identifier and is lower/mixed case or has a trailing space or similar, and your are trying to drop it unquoted, or with a typo or missing space, or something. As you haven't given details it can only be a guess though. – Alex Poole Commented Feb 20 at 17:03
• 3 if it's a public link you have to say drop public database link xyz instead of just drop database link xyz. If its private, you have to be logged in as the link owner to drop it - you cannot drop a link owned by another user, even if you are SYS. Lastly, as Alex mentioned, if there is mixed case, specials chars, trailing spaces, etc.. in the name (which is bad), you'll need to enclose the exact link name (use copy-paste, don't type it) in double quotes. – Paul W Commented Feb 20 at 17:10
• 2 If it's lower case when you query dba_db_links, then you'll have to drop it with drop database link "xyz". Otherwise, if it's all uppercase (as Oracle objects should be), you leave out the quotes. Don't fet the possibility of invisible trailing spaces - that's why I said copy it from dba_db_links rather than typing it in, and then enclose it in double quotes. It is not possible to have two links with the same name under the same schema. If that's what you are seeing in dba_db_links, then one of them has some trailing spaces or other hidden characters – Paul W Commented Feb 20 at 17:16
• 1 query: select dump(db_link),owner,db_link from dba_db_links and compare the dump of the two. See if there is a difference. – Paul W Commented Feb 20 at 17:34
• 1 dump and compare the owner column too. – Paul W Commented Feb 20 at 17:44

 |  Show 6 more comments

1 Answer 1

Sorted by: Reset to default 2

If a database link, or any other Oracle object, is created with double quotes within which there are anything other than uppercase letters and digits and allowable chars _#$, then you will be unable to reference that object in any DDL unless you exactly reduplicate its case-sensitive name and surround it with double-quotes every time:

drop database link "xyz"

That's why it's not worth it - leave out the double quotes when you create objects and allow Oracle to store everything in uppercase, which is always assumed without double quotes.

Secondly, if the link is private, you must be the link owner to drop it. Same with creating a new one. So, log out and log back in with the username and password of the link owner.

There is a workaround for DBAs (or anyone with the create any procedure system priv), however, if you don't know the password: you can create a procedure under another user, and within that procedure drop the link:

create procedure otheruser.dropmylink
as
begin
  execute immediate 'drop database link xyz';
end;
/

begin otheruser.dropmylink; end; -- execute the proc
/

drop procedure otheruser.dropmylink; -- drop the proc
/

It's annoying that we have to use a stupid workaround like this, but it is what it is.