oracle中使用物化視圖實現表的同步

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.

<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 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 ] 
;