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?
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.