Create fast-refresh MV over dblink on table without PK?

Question

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.

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.

---

Question:

Is it possible to create a fast-refresh MV over a dblink on table that doesn't have a PK?

Answer 1

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
Connected.
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.

Then:

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

PL/SQL procedure successfully completed.

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

LAST_REF
--------
FAST

Answer 2

in source db

CREATE MATERIALIZED VIEW LOG ON TABLE_NAME
WITH ROWID INCLUDING NEW VALUES;

in destination

CREATE MATERIALIZED VIEW MV_NAME BUILD
IMMEDIATE REFRESH FAST WITH ROWID 
AS SELECT * from TABLE_NAME@<<dblink>>;

PROCEDURE REFRESH_SNAPS AS
    type recR is record (
        mview_name varchar2(150),
        log_name varchar2(150),
        row_count number
    );

    type recsT is table of recr index by binary_integer;
    
    recs recsT;

   LSQL varchar2(32000) /* or clob if there are a lot of mviews */
    
begin
    lsql := 'select * from(';
    for i in (
        select  ml.*,
                case when rownum > 1 then 'union all 
        ' end ||'select '''||m.mview_name||''' mview_name, '''||ml.log_owner||'.'||ml.log_table||''' table_name, count(1) c from '||ml.log_owner||'.'||ml.log_table||'@<<dblink>> ' sql_text
        from    all_mview_LOGS@<<dblink>> ML,
                ALL_MVIEWS M
        where   ML.MASTER = M.MVIEW_NAME
        AND     M.owner = 'SPEP'
        AND     M.REFRESH_METHOD = 'FAST'
    ) loop

        lsql := lsql ||i.sql_text;
    
    end loop;

    lsql := lsql ||' ) where c > 0';

    execute immediate lsql bulk collect into recs;

    for i in 1..recs.count loop
        BEGIN
            DBMS_APPLICATION_INFO.SET_MODULE('REFRESH_SNAPS',recs(I).MVIEW_NAME);
            DBMS_MVIEW.REFRESH(recs(I).MVIEW_NAME,'?'); 
        EXCEPTION
        WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR(-20001,recs(I).MVIEW_NAME);
        END;
    end loop;

end;

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'RUN_REFRESH_SNAPS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN REFRESH_SNAPS; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTLY; FREQUENCY=1;',
    end_date        => NULL,
    enabled         => TRUE);

    DBMS_SCHEDULER.RUN_JOB ('RUN_REFRESH_SNAPS');

END;