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.
Question:
Is it possible to create a fast-refresh MV over a dblink on table that doesn't have a PK?