oracle中使用物化视图实现表的同步

表的同步可以使用物化视图来实现，可以是同库的也可以是不同数据库之间进行数据同步，建议在同库的数据同步可以使用实时的同步，如果使用db link建议使用增量的刷新方式，防止dblink导致原库的性能急剧下降。

主要步骤：

1.在原表上建立物化视图日志

2.在创建目标表

3.创建与目标表同名的物化视图日志

1.创建原表和物化视图日志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.创建目标表和物化视图注意这里我创建是refresh fast on commit类型的物化视图SQL> create table t2 as select * from t1 where 1=2;Table created.SQL> CREATE MATERIALIZED VIEW  t2 on prebuilt table refresh fast on commit  as select * from t1;Materialized view created.

3.简单测试在t1插入一条数据，一提交t2即存在数据SQL> insert into t1 values(1,'A');1 row created.SQL> commit;Commit complete.SQL> select * from t2;ID NAME---------- ------------------------------ 1 A

4.ddl测试通过测试我们发现物化视图不支持ddl语句我们给t1添加一个列和rename一个列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 NAME---------- ------------------------------ 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我们发现数据没有过来，我们看一下物化视图的定义和状态 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 ON COMMIT  WITH PRIMARY KEY USING 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此时物化视图为编译错误。

5.重新创建一个demand物化视图  SQL> CREATE MATERIALIZED VIEW  t2 on prebuilt table refresh fast on demand as select * from t1;Materialized view created.SQL> select * from t2;no rows selectedSQL> exec dbms_mview.refresh('T2','c');  --手工全量刷新PL/SQL procedure successfully completed.SQL> select * from t2;ID NAMES  SF---------- ------------------------------ ---------- 2 B 3 X 123SQL> insert into t1 values(4,'Y',88);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');  --手工增量刷新PL/SQL procedure successfully completed.SQL> select * from t2;ID NAMES  SF---------- ------------------------------ ---------- 2 B 3 X 123 4 Y  88

6.创建物化视图的语句官方文档：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 [encryption_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.创建物化视图日志的语句官方文档：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 ] ;