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!

Albuminate

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;

Narayan

To share a database link to all users use the 'PUBLIC' directive. CREATE PUBLIC DATABASE LINK ... – Josephus

Answer 2

I had the same problem I used the solution offered above - I dropped the SYNONYM, created a VIEW with the same name as the synonym. it had a select using the dblink , and gave GRANT SELECT to the other schema It worked great.

Boyce

Answer 3

You can also create a view at SCHEMA B which points to dblink query. Of course you will need some privilege but it can solve your problem. Check the view script below:

create view SCHEMA_B.mytable_view as select * from mytable@db_link;
GRANT SELECT ON SCHEMA_B.mytable_view to MYUSER;

Now you can select data from SCHEMA_B.mytable_view.

Jankell