Distributed Query Involving a Database Opened in Read Only Mode Produces Wrong Results (Doc ID 1517910.1)

Last updated on MARCH 07, 2019

Applies to:

Symptoms

A query returns wrong results, and those results seem to be from time in the past.

Query executed involve 2 or more databases, where one database (local or remote) is open in read-only mode.

Sample of the issue when remote databases are in read-write mode and local in read-only mode:

1) Initial situation. Execute in local db:

select e.ename, v.dname from myemp e, mydept@TB2030 v where e.deptno=v.deptno;

ENAME      DNAME
---------- --------------
MILLER     ACCOUNTING
KING       ACCOUNTING
CLARK      ACCOUNTING
FORD       RESEARCH
ADAMS      RESEARCH
....
....

2) Update on remote table

update mydept set dname = 'AAAA' where deptno=10;
update mydept set dname = 'BBBB' where deptno=20;
update mydept set dname = 'CCCC' where deptno=30;
update mydept set dname = 'DDDD' where deptno=40;
commit;

3) In a new session in local db, still old values are returned:

select e.ename, v.dname from myemp e, mydept@TB2030 v where e.deptno=v.deptno;

ENAME      DNAME
---------- --------------
MILLER     ACCOUNTING
KING       ACCOUNTING
CLARK      ACCOUNTING
FORD       RESEARCH
ADAMS      RESEARCH
....
....

When expected results are (returned properly in read-write mode):

select e.ename, v.dname from myemp e, mydept@TB2030 v where e.deptno=v.deptno;

ENAME      DNAME
---------- --------------
MILLER     AAAA
KING       AAAA
CLARK      AAAA
FORD       BBBB
ADAMS      BBBB
....
....

Changes

One database involved in the query has been opened in read-only mode.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.