Synchronizing tables with materialized views in Oracle

Table synchronization can be implemented using materialized views, can be the same library or can be different databases for data synchronization, it is recommended that data synchronization in the same library can use real-time synchronization, if using the DB link is recommended to use incremental refresh, to prevent Dblink caused the performance of the original library dropped sharply.

Main steps:

1. Creating materialized view logs on the original table

2. When creating a target table

3. Create a materialized view log with the same name as the target table

1. Create the original table and materialized view Log sql> conn bre/breconnected.sql> CREATE TABLE t1 (ID int,name varchar2 (30)); Table created. Sql> ALTER TABLE t1 ADD constraint Pk_t1 primary key (ID) using index; Table altered. Sql> create materialized view Log on T1 with primary key; Materialized view log created.

<pre name= "code" class= "SQL" >2. Create a target table and materialized view note here I create a materialized view of the refresh fast on commit type sql> CREATE TABLE t2 as SELECT * FR Om T1 where 1=2; Table created. Sql> CREATE materialized VIEW  T2 on prebuilt table refresh fast in commit as  select * from T1; Materialized view created.

3. Simple test inserts a data in T1, a commit t2 that exists data sql> insert INTO T1 values (1, ' A '); 1 row created. Sql> commit; Commit complete. Sql> SELECT * from T2;id NAME----------------------------------------1 A

4.DDL Test passed testing we found that materialized views do not support DDL statements we add a column to T1 and rename a column sql> ALTER TABLE t1 add SF int; Table altered. sql> ALTER TABLE T1 rename column name to names; Table altered. Sql> SELECT * from T1;id NAMES SF--------------------------------------------------2 bsql> select * FROM T2;id N AME----------------------------------------2 B sql> insert INTO T1 values (3, ' X ', 123); 1 row created. Sql> commit; Commit complete.  Sql> SELECT * from T1;id NAMES SF--------------------------------------------------2 B 3 X 123sql> SELECT * FROM T2;id NAME----------------------------------------2 b We find that the data is not coming, so let's take a look at the definition and status of materialized views sql> Select Dbms_metadata.get_ DDL (' Materialized_view ', ' T2 ') from Dual;dbms_metadata. GET_DDL (' Materialized_view ', ' T2 ')----------------------------------------------------------------------------- ---CREATE materialized VIEW "BRE". T2 "(" ID "," NAME ") on prebuilt TABLE without reduced PRECISION USING INDEX REFRESH FAST in COMMIT with PRIMARY KEY USI NG DEFAULT LOCAL rollbACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE as select "T1". " ID "id", "T1". " Name "" Name "from" T1 "" T1 "sql> select staleness from User_mviews; Staleness-------------------Compilation_error The materialized view is a compilation error at this time.

5. Recreate a demand materialized view  sql> create materialized view  T2 on prebuilt table refresh fast in demand as SELECT * from t 1; Materialized view created. Sql> SELECT * from t2;no rows selectedsql> exec dbms_mview.refresh (' T2 ', ' C ');  --Manual full volume refresh PL/SQL procedure successfully completed. Sql> SELECT * from T2;id NAMES  SF--------------------------------------------------2 B 3 X 123sql> Insert int o T1 VALUES (4, ' Y ', n); 1 row created. Sql> commit; Commit complete. Sql> SELECT * from T2;id NAMES  SF--------------------------------------------------2 B 3 X 123sql> exec Dbms_ Mview.refresh (' T2 ', ' f ');  --Manual incremental refresh of PL/SQL procedure successfully completed. Sql> SELECT * from T2;id NAMES  SF--------------------------------------------------2 B 3 X 123 4 Y  88

6. Creating statements for materialized views Official document: Http://docs.oracle.com/database/121/SQLRF/statements_6002.htm#SQLRF01302CREATE materialized view [Schema.] Materialized_view  [of [Schema.] object_type]  [({scoped_table_ref_constraint      | column_alias [ENCRYPT [en] CRYPTION_SPEC]]      }      [, {scoped_table_ref_constraint         | column_alias [ENCRYPT [Encryption_spec]]         }      ]...    )  ]  {on prebuilt TABLE    [{with | Without} reduced PRECISION]  | physical_properties materialized_view_props  }  [USING INDEX    [ Physical_attributes_clause    | Tablespace tablespace    ] ...  | USING NO INDEX  ] [  Create_mv_refresh]  [for UPDATE]  [Evaluation_edition_clause]  [Query_ Rewrite_clause]as subquery;

7. Creating statements for materialized view logs Official document: Http://docs.oracle.com/database/121/SQLRF/statements_6003.htm#SQLRF01303CREATE materialized VIEW LOG on [schema.] Table  [Physical_attributes_clause  | Tablespace tablespace  | logging_clause  | {CACHE | NOCACHE}  ] ...  [Parallel_clause]  [Table_partitioning_clauses]  [with [{OBJECT ID         | PRIMARY KEY         | ROWID         | SEQUENCE         | Commit SCN         }           [{, OBJECT ID             |, PRIMARY KEY             |, ROWID             |, SEQUENCE             |, commit SCN             }
   ] [...]    (column [, column] ...)    [New_values_clause]  ] [Mv_log_purge_clause];

Synchronizing tables with materialized views in Oracle