4

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:

  • One is a private dblink which uses account A to access the Foobar database
  • The other is a public dblink to the same Foobar database which uses account B for its access

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?

1 Answers1

1

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.

J91321
  • 697
  • 1
  • 7
  • 19
  • Thanks for this quote... adding the link where you took that from: https://docs.oracle.com/cd/B12037_01/server.101/b10759/sql_elements009.htm –  Dec 16 '15 at 13:42
  • From what I'm seeing, I would have to agree with your assessment. It appears that I'll need a work around to access both DBLinks. Thanks! –  Dec 16 '15 at 13:43