How to use synonym of a DBlink in Oracle?

Question

I have created a synonym for a dblink.

create synonym dblink2 for dblink1

But when I query anything using the synonym instead of the dblink, I'm getting connection description for remote database not found error.

SELECT * FROM DUAL@DBLINK2

How do I query using the synonym?

Edit: I know that it'll work if I create a view of the table using dblink. But my requirement is the above question.

Answer 1

I don't see the point in creating a synonym for the dblink itself. Ideally you create the synonym for the remote table using the dblink.

CREATE DATABASE LINK my_db_link CONNECT TO user IDENTIFIED BY passwd USING 'alias';
CREATE SYNONYM my_table FOR remote_table@my_db_link;

Now, you could query the remote table using the synonym:

SELECT * FROM my_table;

Answer 2

Unfortunately creation of synonyms for dblinks is not supported. If you read the documentation on synonyms, you will find that the permitted objects for synonyms are only:

Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.

The reason why your second query fails is that the synomym you have created is not functioning correctly. It is not being validated properly at creation time, and you can create any sort of incorrect synonyms like that. To verify, just test the following statement:

create synonym dblink3 for no_object_with_this_name;

You will still get a response like this:

*Synonym DBLINK3 created.*

But of course nothing will work via this synonym.

Answer 3

I'm trying to think of the business issue that gets solved by putting a synonym on a db_link, and the only thing I can think of is that you need to deploy constant code that will be selecting from some_Table@some_dblink, and although the table names are constant different users may be looking across different db_links. Or you just want to be able to swap which db_link you are operating across with a simple synonym repoint.

Here's the problem: it can't be done that way. db_link synonyms are not allowed.

Your only solution is to have the code instead reference the tables by synonyms, and set private synonyms to point across the correct db_link. That way your code continues to "Select from REMOTE_TABLE1" and you just can flip which DB_LINK you are getting that remote table from.

Is it a pain to have to set/reset 100+ private synonyms? Yep. But if it is something you need to do often then bundle up a procedure to do it for you where you pass in the db_link name and it cycles through and resets the synonyms for you.