0

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?

1
  • 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.”
    – User1974
    Dec 3, 2020 at 23:33

2 Answers 2

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
3
  • 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.
    – User1974
    Dec 8, 2020 at 17:41
  • @User1973 The above created a unique index along with the unique constraint in the version I tested (19.9). Dec 8, 2020 at 18:39
  • This answer really helped me. Thank you very much.
    – User1974
    Dec 10, 2020 at 22:57
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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