DB-Server 1:
CREATE TABLE foo (id NUMBER);
INSERT INTO foo VALUES (1);
COMMIT;
DB-Server 2:
CREATE DATABASE LINK foo_link... -- Points to Schema of DB-Server 1.
CREATE MATERIALIZED VIEW mv_foo REFRESH FORCE
AS
SELECT * FROM foo@foo_link;
SELECT * FROM mv_foo; -- 1 row as expected
DB-Server 1:
DROP TABLE foo;
CREATE TABLE foo (id NUMBER);
INSERT INTO foo VALUES (1);
COMMIT;
DB-Server 2:
BEGIN
DBMS_MVIEW.REFRESH(list => 'MV_FOO'); -- No errors.
END;
/
SELECT * FROM mv_foo; -- 0 rows!
What could be the reason for the empty mview after recreating the remote table? Thx.
DB-Server 1: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
DB-Server 2: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
UPDATE (2022-07-11T15:25:00)
The mview at DB-Server 2 is refreshed every hour by DBMS_MVIEW.REFRESH(list => 'MV_FOO')
. The table FOO
at DB-Server 1 was deleted and new created between the refresh interval mentioned above. I figured out this by SELECT created FROM dba_objects@foo_link where object_name = 'FOO'
. This was the current date/time. After one hour of emptiness of mview MV_FOO
all gets right and the mview wasn't empty anymore. I figured out this by SELECT COUNT(1) FROM mv_foo AS OF TIMESTAMP ...
. So, it was a temporary problem. But the reason for one hour of emptiness of MV_FOO
remains a mystery.