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



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

主要步骤：

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

2.在创建目标表

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

 
1.创建原表和物化视图日志
SQL> conn bre/bre
Connected.
 
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.
 
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 B
 
SQL> 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                     123
 
SQL> 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 selected
 
SQL> exec dbms_mview.refresh('T2','c');  --手工全量刷新
 
PL/SQL procedure successfully completed.
 
SQL> select * from t2;
 
    ID NAMES                  SF
---------- ------------------------------ ----------
     2 B
     3 X                     123
 
SQL> 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                     123
 
SQL> 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#SQLRF01302
CREATE 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#SQLRF01303
 
CREATE 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 ]
;