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

User · Answer

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

User · Answer

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

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

Examples related to database

Examples related to oracle

Examples related to database-schema

Examples related to dblink