CODEWITHSUNDEEP
databaseoracle-databasedatabase-schemadblink
Steffen
Steffen

Reputation: 171

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

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

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!

Upvotes: 17

Views: 321968

Answers (4)

Darin Rodrigues
Darin Rodrigues

Reputation: 1

To start with the original question does not in any way say that SCHEMA_DB is in a different Database, However if it is so, The db_link has to be defined in Schema MYUSER for it to work and avoid the message the user is receiving.

if however the Schema_B is in the same database then GRANT Select on the SCHEMA_B to user MYUSER should suffice.

Upvotes: 0

postgresnewbie
postgresnewbie

Reputation: 1468

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.

Upvotes: 0

user14570900
user14570900

Reputation: 131

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.

Upvotes: 0

GTG
GTG

Reputation: 4954

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;

Upvotes: 22

Related Questions