資料複製_物化檢視

對於變化不頻繁的小表,可以使用物化檢視實現跨資料庫資料同步。
1,源端,建立物化檢視日誌
CREATE MATERIALIZED VIEW LOG ON org
   WITH PRIMARY KEY INCLUDING NEW VALUES;

2,目標端,建立資料庫連線
create database link net_db_link
connect to net identified by net
--using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = oradb1)))';
using '(DESCRIPTION=(LOAD_BALANCE=on)(FAILOVER=ON)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.129.203)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=22.188.129.205)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=oraNET)))';

3,目標端,建立物化檢視
CREATE MATERIALIZED VIEW org
REFRESH  FAST START WITH SYSDATE NEXT  SYSDATE + 1/24
WITH PRIMARY KEY  as  select * from ;

4,維護
--手工完全重新整理mv
begin
  DBMS_MVIEW.REFRES('org','c');
end;
/

--手工重新整理mv
begin
 DBMS_MVIEW.REFRESH('org','f');
end;
/
--&gtrefresh後，會刪除不再需要的log

--使用物化檢視id(從user_base_table_mviews中獲取)刪除log
begin
  DBMS_MVIEW.PURGE_MVIEW_FROM_LOG(mview_id);
end;
/

--使用基表刪除log
begin
  DBMS_MVIEW.PURGE_LOG('org',9999);
end;
/