How to SELECT in Oracle using a DBLINK located in a different schema?

Question

We have an Oracle DBMS (11g) and the following configuration:

• A DB user "MYUSER"
• Two schemas "MYUSER" and "SCHEMA_B"
• User "MYUSER" can access "SCHEMA_B" and has READ permissions on its tables
• A public DB link "DB_LINK" located in "SCHEMA_B"
• The DB_LINK is working when using the DB user "SCHEMA_B" directly

Question: When logged on as "MYUSER", what is the correct syntax to access tables using the DB link of "SCHEMA_B"? Is it possible to do so at all?

I already tried several constellations, which all did not work:

select * from dual@"DB_LINK"
select * from dual@"SCHEMA_B"."DB_LINK"
select * from dual@SCHEMA_B."DB_LINK"
select * from dual@SCHEMA_B.DB_LINK
select * from SCHEMA_B.dual@DB_LINK
select * from "SCHEMA_B".dual@DB_LINK

The error message I receive is: ORA-02019. 00000 - "connection description for remote database not found"

Thanks for any suggestion!

Answer 1

I don't think it is possible to share a database link between more than one user but not all. They are either private (for one user only) or public (for all users).

A good way around this is to create a view in SCHEMA_B that exposes the table you want to access through the database link. This will also give you good control over who is allowed to select from the database link, as you can control the access to the view.

Do like this:

create database link db_link... as before;
create view mytable_view as select * from mytable@db_link;
grant select on mytable_view to myuser;