对于变化不频繁的小表,可以使用物化视图实现跨数据库数据同步。
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 org@NET_DB_LINK;
4,维护
--手工完全刷新mv
begin
DBMS_MVIEW.REFRES('org','c');
end;
/
--手工刷新mv
begin
DBMS_MVIEW.REFRESH('org','f');
end;
/
-->refresh后,会删除不再需要的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;
/
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-696846/,如需转载,请注明出处,否则将追究法律责任。