René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Primary key materialized view replication in Oracle

DB to:
This db needs a database link to the DB from in order to retrieve the data. 
create public database link db_to.foo.invalid 
  connect to rene 
  identified by rene 
  using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=db_to.foo.invalid)(Port=1530)))(CONNECT_DATA=(SID=db_to)))';
DB from:
Here's the table that is going to be replicated: 
create table to_be_replicated (
  a    number        primary key,
  b    varchar2(20)
);
There needs to be a primary key on the table as otherwise, we'd be going to hit an ORA-12014 table 'TO_BE_REPLICATED' does not contain a primary key constraint later on.
DB to: 
create materialized view replicated_table 
for update 
as
select * from rene.to_be_replicated@db_from.foo.invalid
DB from: 
insert into to_be_replicated values (1, 'one');
commit;
DB to: 
select * from replicated_table;
Nothing is returned. We need to refresh the materialized view: 
begin
  dbms_mview.refresh('REPLICATED_TABLE');
end;
/
Now, select ... from replicated_table will return the values inserted into to_be_replicated.
However, a fast refresh would return a ORA-23413: table "RENE"."TO_BE_REPLICATED" does not have a materialized view log.
So, let's create such a materialized view log: 
create materialized view log on to_be_replicated;
Now, here's what happens if we try to fast refresh the materialized view: 
begin
  dbms_mview.refresh('REPLICATED_TABLE', 'F');
end;
ORA-12034: materialized view log on "RENE"."TO_BE_REPLICATED" younger than last refresh
First, we have to do a complete refresh, then we can start to fast refresh the table.