Oracle 11g select from three databases using two dblinks

Question

I have three databases M,O,S.
M has two DBLINKs to O and S.
I can select from O while connected to M like this:

SELECT * FROM TBL1@O_DBLINK;

and also select from S while connected to M like this:

SELECT * FROM TBL2@S_DBLINK;

but when I try to select from both DBLINKs in a single script like this:

DECLARE
    a number;
    b number;
BEGIN
    SELECT colA into a FROM TBL1@O_DBLINK;
    SELECT colB into b FROM TBL2@S_DBLINK;
END;

it returns following error:

ORA-04052: error occurred when looking up remote object S.TBLB@S_DBLINK ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database open for read-only access

The very strange is that when I change the order of selects always the latter returns error:

DECLARE
    a number;
    b number;
BEGIN
    SELECT colB into b FROM TBL2@S_DBLINK;
    SELECT colA into a FROM TBL1@O_DBLINK;
END;

ORA-04052: error occurred when looking up remote object O.TBLA@O_DBLINK ORA-00604: error occurred at recursive SQL level 1 ORA-16000: database open for read-only access

Answer 1

Please try this approach,

Create table1 in Database M and create SYNONYM of that same table (table1) in remaining database O and S.

Now you can do all manipulations.

for example:

create table table1 (col1); -- database M

create public synonym table1 for table1; -- database O & S

Then now you can access the table with DB_Link and data will also reflected to all DB.

Refer the link to CREATE SYNONYM