I want to create a materialized view between a primary system (Oracle 18c) and an external system (Oracle 19c) via a dblink.
- The MV would refresh on a 30-second schedule (since on-commit is not possible over a dblink).
- The table in the external system does not have a primary key.
I'm a novice. Here's what I've tried:
--in the external system: create materialized view log on external_system.workorder with rowid; --in the primary system: create materialized view primary_system.workorder_mv build immediate refresh fast start with sysdate next sysdate + (30/(60*60*24)) as select cast(workorderid as number(38,0)) as objectid, wonum, status, --other fields longitudex, latitudey from external_system.workorder@gistomax
When I try the above, I get a litany of errors, starting with one that says I need a primary key.
I found a page that suggests that it's possible to do a fast refresh over a dblink. But the example uses a primary key, not a rowid.
Rowid materialized views are not eligible for fast refresh after a master table reorganization until a complete refresh has been performed.
But I don't really know what that means. And to be honest, I'm so new at this, that I know I'm barking up the wrong tree, so I'll stop there.
Is it possible to create a fast-refresh MV over a dblink on table that doesn't have a PK?