Create an incremental synchronization Oracle materialized view problem

We used the Oracle materialized view incremental refresh mechanism to synchronize the data of a table on database A to another database B on a regular basis.

The simplest implementation step we often use is this. First, confirm on database A that the table has a primary key, and then build on this table to establish materialized view logs such as "Create materialized view Log on T_tablename", and then to the materialized views on database B for creating database links and quick refreshes such as "create Materialized view Mv_tablename refresh fast on demand start and sysdate next sysdate+1/288 as SELECT * from T_tablename@d Blink_name; ".

Now, the PRIMARY KEY constraint for this table on database A is disable by the duplicate of the primary key field value, and the new materialized view on the third database C fails, saying: "ORA-12014: Table ' t_mv_test ' does not contain a PRIMARY key constraint." If you add the primary key of this table to the field and then do so, then say: "ORA-23412: The primary key column for the primary table has changed."

But there is a strange phenomenon: we have also established materialized views on database B, which are disable after the primary key of this table.

(Miki West Tour @mikixiyou original link: http://mikixiyou.iteye.com/blog/1753779)

Let's introduce the process of creating materialized views on database B.

First, introduce the environment.

The version of database A and database B is Oracle 10.2.0.4 for Linux x86 64bit.

The tables that are synchronized on database A are structured as follows:

CREATE TABLE T_mv_test  
(  
  A date,  
  B date,  
  C date
);  
       
ALTER TABLE t_mv_test  
  add constraint pk_t_mv_test primary key (A,B);

This is a test table, very simple 3 fields.

Next, create the materialized view log

To create a materialized view log of this table on database A, the creation method we use is the most concise, as follows:

CREATE materialized VIEW LOG on T_mv_test;

This creates a trigger and a log table mlog$_t_mv_test on the table t_mv_test.

It is the same as the create materialized VIEW LOG on t_mv_test with PRIMARY key, which omits the operation of the with PRIMARY key.

However, this log table can only capture the deletion and increment of primary key fields, and if the values of the Non-key fields change, the past is not synchronized. This is the limitation of this simple method.

Finally, create a materialized view

Create a materialized view of a scheduled incremental update on database B as follows:

Create materialized view T_mv_test  
refresh fast on demand to  
start with sysdate next  
sysdate+1/288
as SELECT * from T_mv_test@dblink_name;

This is the simplest way to create a materialized view of the incremental update mechanism, which synchronizes the changed records to another database B every 5 minutes by checking the deletion and insertion of the primary key fields for this table on database A.

This process also omits the WITH primary key keyword, which is also the default value.

Create materialized view T_mv_test  
refresh fast on Demand  
start with Sysdate next sysdate+1/288 with primary key- -Default value  
as
select * from T_mv_test@dblink_name;