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

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 IMMEDIATE 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

CodePudding user response：

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.

CodePudding user response：

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';