0

This Question is about Oracle Database.

I have two remote databases DB_1 and DB_2, and I have two schemas on each database: Schema DB_1 and Schema DB_2 on database DB_1, and Schema DB_1 and Schema DB_2 on database DB_2. I want to reach database DB_2 Schema DB_2 from database DB_1 Schema DB_1. For example: when I try this query in DB_1 from schema DB_1:

SELECT * FROM DB_2.example_table

I got an error because there is not table "example_table" in DB_2 Schema on the DB_1 database. But, when I try the same query on DB_2 Schema DB_2, I got the correct result (some data).

So, I need to tune my databases in some way so I can SELECT * FROM DB_2.example_TABLE from DB_1 Database.

But, there is another problem, I don't have permission to CREATE LINK. However, when I query SELECT * FROM ALL_LINKS; on my production database DB_1 schema DB_1 - there are no links at all. So, I need to figure out which way my production Databases was linked (unfortunately my colleagues don't know either). On my production database DB_1 schema DB_1 I can fetch data with the query:

SELECT * FROM DB_2.example_table

And the data I got is the same if I try to query the same on DB_2 production database DB_2 schema.

But there are no public\private Links on DB1/DB2 production databases at all. Is there another way to create a "connect" between my databases? (I queried SELECT * FROM ALL_LINKS from DB_1 schema DB_1, is this possible so I can fetch some other data if I try the query on DB_1 schema DB_2?)

Thanks in anticipation!

0

Several issues, in no particular order.

Your SELECT syntax is wrong. To select a table from another database the syntax is

select some_columns from remote_table@remote_db;

Note the "@remote_db". Here 'remote_db' refers to an entry in the local database's tnsnames.ora file, which in turn resolves to the remote db in question. CORRECTION: Here 'remote_db' is the name of the database link.

Second, you say you have no permission to create a db link. Well, someone is going to have to create one. That is the mechanism by which one database queries another. Period. Full stop.

Third, you say you find no db links when you query ALL_LINKS. The name of the view is ALL_DB_LINKS. And that only shows the links that the current user has permissions on. If you want to see all links in the database, you query DBA_DB_LINKS, as a user that has permissions to query that view. Plus, I see nothing in your question description that would cause me to suspect that there indeed are any db links.

1
  • Hello Ed, Thank you very much! Your answer is very helpful.
    – IaroslavP
    May 22 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.