Oracle – Connect Two Remote Databases as Schemas

database-linkoracleoracle-12c

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!

Best Answer

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.

Related Solutions

Best method to give two Oracle databases on the same server access to common data

How exotic do you want to get ?

Uwe Hesse demonstrated that it is possible to have read-only tablespaces shared between two independent (non-RAC) database instances. I'd have to be very sure that the disk-space saving was worth it before I'd configure an environment this way.

Sql-server – How to connect multiple users/schemas in Oracle 11g

You need to grant the REFERENCES privilege on the reference table to user2. (See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)

Here's a demo:

SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

SQL> create user user2 identified by user2;
User created.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.

SQL> create table user1.ref_table (id number primary key);
Table created.
SQL> insert into user1.ref_table values (1);
1 row created.
SQL> insert into user1.ref_table values (2);
1 row created.
SQL> commit;
Commit complete.

SQL> grant references on user1.ref_table to user2;
Grant succeeded.

SQL> connect user2/user2;
Connected.
SQL> create table oth_table (thing number, fk number);
Table created.
SQL> alter table oth_table add(constraint fk1 foreign key (fk)
  2      references user1.ref_table on delete set null);
Table altered.

SQL> insert into oth_table values (42, 1);
1 row created.
SQL> insert into oth_table values (256, 2);
1 row created.
SQL> commit;
Commit complete.

SQL> connect user1/user1;
Connected.
SQL> delete from ref_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

SQL> connect user2/user2;
Connected.
SQL> select * from oth_table;

     THING     FK
---------- ----------
    42
       256      2

If you want admin to do all the work, it'll need the CREATE ANY INDEX privilege, and you still need the REFERENCES grant to user2.

Here's how that could work:

SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> create user admin identified by admin;
User created.

--- Grants
----------------
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.
SQL> grant create session, create any table,
  2        create any index, alter any table to admin;
Grant succeeded.

--- Create reference table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user1.ref_table (id number primary key);
Table created.

--- Do the "references" grant
----------------
SQL> connect / as sysdba
Connected.
SQL> grant references on user1.ref_table to user2;
Grant succeeded.

--- Create the second table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user2.oth_table (foo number, bar number);
Table created.

--- Add the constraint
----------------
SQL> alter table user2.oth_table add(constraint fk
  2      foreign key (bar) references user1.ref_table(id)
  3      on delete set null);
Table altered.

Best Answer

Related Solutions

Best method to give two Oracle databases on the same server access to common data

Sql-server – How to connect multiple users/schemas in Oracle 11g

Related Question