oracle数据库同步dblink.doc

-下面两种建立dblink方法人选一种-远程连接数据流-1、-目的数据库 创建dblink -CREATE PUBLIC DATABASE LINK 数据库链名称 CONNECT TO 用户名 identified by 密码 using (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 远程服务器IP)(PORT = 端口)(CONNECT_DATA =(service_name=连接到数据库服务名);例：CREATE PUBLIC DATABASE LINK dblink_orcl_bql_scan CONNECT TO emuser_bql identified by empsw_bql using (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 61)(PORT = 1521)(CONNECT_DATA =(service_name=orcl);-2、-目的数据库 创建dblinkdrop public database link dblink_orcl_bql_scan;Create public DATABASE LINK dblink_orcl_bql_scan CONNECT TO emuser_bql IDENTIFIED BY emuser_bql USING orcl;-在 源数据库上，创建要同步表的快照日志Create snapshot log on basedata_leechdom; Create snapshot log on basedata_therapyitem;-第二步-在目的数据库-建立快照-Create snapshot SNM_LEECHDOMID as select * from basedata_leechdomdblink_orcl_bql_scan;Create snapshot SNM_ITEMID as select * from basedata_therapyitemdblink_orcl_bql_scan;Alter snapshot SNM_LEECHDOMID refresh fast Start with sysdate next sysdate with primary key;Alter snapshot SNM_ITEMID refresh fast Start with sysdate next sysdate with primary key;-在目的数据库建立触发器-注：单个执行-create or replace trigger sn_delete after delete on SNM_LEECHDOMID for each row begin delete from basedata_leechdom where LEECHDOMID=:old.leechdomid;end;-create or replace trigger sn_insertafter insert on SNM_LEECHDOMID for each row begin insert into basedata_leechdom(leechdomid, leechdomcode, leechdomname, feesort, feesortname, paymethod, paymethodname, type, scope, memorycode, isleechdom, parentid, ismxb, isjsb, standard)values(:new.leechdomid,:new.leechdomcode,:new.leechdomname,:new.feesort,:new.feesortname,:new.paymethod,:new.paymethodname,:new.type,:new.scope,:new.memorycode,:new.isleechdom,:new.parentid,:new.ismxb,:new.isjsb,:new.standard);end;-create or replace trigger sn_update after update on SNM_LEECHDOMID for each row begin update basedata_leechdom set leechdomcode=:new.leechdomcode, leechdomname =:new.leechdomname, feesort=:new.feesort, feesortname=:new.feesortname, paymethod=:new.paymethod, paymethodname=:new.paymethodname, type=:new.type, scope=:new.scope, memorycode=:new.memorycode, isleechdom=:new.isleechdom, parentid=:new.parentid, ismxb=:new.ismxb, isjsb=:new.isjsb, standard=:new.standard where LEECHDOMID=:old.leechdomid;end;-create or replace trigger th_deleteafter delete on SNM_ITEMID for each row begin delete from basedata_therapyitem where itemid = :old.itemid;end;-create or replace trigger th_insert after insert on SNM_ITEMID for each row begin insert into basedata_therapyitem (itemid, itemcode, name,unit, feesort,feesortname, paymethod,paymethodname, exceptioncont,description,meaning, itemsort,isitem, parentid,memorycode)values(:new.ITEMID,:new.itemcode,:,:new.unit,:new.feesort,:new.feesortname,:new.paymethod,:new.paymethodname,:new.exceptioncont,:new.description,:new.meaning,:new.itemsort,:new.isitem,:new.parentid,:new.memorycode);end;-create or replace trigger th_updateafter update on SNM_ITEMID for each row begin update basedata_therapyitem set itemid = :new.itemid, itemcode = :new.itemcode, name = :, unit = :new.unit, feesort = :new.feesort, feesortname = :new.feesortname, paymethod = :new.paymethod, paymethodname = :new.paymethodname, exceptioncont = :new.exceptioncont, description = :new.description, meaning = :new.meaning, itemsort = :new.itemsort, isitem = :new.isitem, parentid = :new.parentid, memorycode = :new.memorycode where itemid = :old.itemid;end;-测试-查看快照-SNM_LEECHDOMID 和SNM_ITEMID 运行状况-在目的数据库中执行-SELECT NAME,