MView from remote table over database link gets empty after recreation of table

Question

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.

Answer 1

Materialized views are linked to their source tables by object_id, not by name. If the source table is recreated, the relationship to the MV is broken and the MV must be recreated as well.

Answer 2

I'm unable to reproduce this, so don't know what's going on here.

Here are some debugging steps to help you:

Run the query in the MV (SELECT * FROM foo@foo_link) on the local database and check it returns one row

As you've dropped & created the table, is there any chance the MV query references a different FOO object in the remote database (e.g. a public synonym)?

Is there any chance this could be explained by DELETE activity on the remote database?

Verify the state of the MV with:

select last_refresh_type, last_refresh_date, compile_state 
from   user_mviews
where  mview_name = 'MV_FOO';