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))
    cast(workorderid as number(38,0)) as objectid,
    --other fields

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.

And the docs say:

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?

  • From a colleague: "Unless the remote table has a pk or unique column, fast refresh is not possible. I would expect that rowid would not work, because it is not immutable. In other words, a row's rowid can change independently of the data. So, if there isn't a pk or unique column on the remote table, then fast refresh is not possible.”
    – User1973
    Dec 3 '20 at 23:33

As already demonstrated in your other question, a unique constraint on a NOT NULL column is good enough to create an mview log with "PK" values. That can be used for fast refresh.

SQL> conn bp/bp
SQL> create database link self connect to bp identified by bp
     using 'localhost:1521/RYMIN19';

Database link created.

SQL> create table t1 (c1 number not null unique, c2 number);

Table created.

SQL> create materialized view log on t1 with primary key;

Materialized view log created.

SQL> create materialized view mv1 refresh fast on demand as select * from t1@self;

Materialized view created.

SQL> insert into t1 values (1, 1);

1 row created.

SQL> insert into t1 values (2, 2);

1 row created.

SQL> commit;

Commit complete.


SQL> exec dbms_mview.refresh('MV1', 'f');

PL/SQL procedure successfully completed.

SQL> select last_refresh_type from user_mviews where mview_name = 'MV1';

  • Does "unique" in create table t1 (c1 number not null unique, c2 number); create a unique index? Or is it an actual constraint on the column (just like not null)? I ask because I'm not sure if the WORKORDERID column in Maximo has a unique constraint, or just a unique index.
    – User1973
    Dec 8 '20 at 17:41
  • @User1973 The above created a unique index along with the unique constraint in the version I tested (19.9). Dec 8 '20 at 18:39
  • This answer really helped me. Thank you very much.
    – User1973
    Dec 10 '20 at 22:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.