CODEWITHSUNDEEP
sqloracle-databasepublicdblink
user2858650
user2858650

Reputation:

Explicitly refer to public DBLink when schema owns a private one of same name?

I'm working a migration project requiring an import of several Oracle database schema onto an existing database. This requirement has brought about an interesting conflict where I now have two dblinks with the same name:

Global Names is set to true so I cannot change the names of these dblinks.

I've already figured out through trial and error that when signed into schema that owns the private dblink that the following:

SELECT * 
FROM table@foobar;

will refer to the private dblink and not the public one. But for situations where I require the account B privileges, I cannot figure out how to explicitly refer to the public dblink.

Does anyone know of syntax I can use to refer to the public @foobar?

Upvotes: 4

Views: 2552

Answers (1)

J91321
J91321

Reputation: 727

From Oracle documentation.

Oracle first searches for a private database link in your own schema with the same name as the database link in the statement. Then, if necessary, it searches for a public database link with the same name.

I don't think this can be changed in any way. Not that I know of or found in documentation. You could create public synonym but that will work only if you need to access with B specific objects. Synonym can't be created for whole database link.

Wouldn't it be easier to turn global names to False on session level and create new link to B with otherwise invalid link name. If you change global names on session level only that session will be allowed to use new link.

Upvotes: 1

Related Questions