1、-下面两种建立 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
2、CONNECT TO emuser_bql identified by empsw_bql using (DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.193.161)(PORT = 1521)(CONNECT_DATA =(service_name=orcl);-2、 -目的数据库 创建 dblinkdrop public database link dblink_orcl_bql_scan;Create public DATABASE LINK dblink_orcl_bql_scan CONNE
3、CT 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 *
4、 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
5、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, paymethodn
6、ame, 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
7、);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=:n
8、ew.type,scope=:new.scope,memorycode=:new.memorycode,isleechdom=:new.isleechdom,parentid=:new.parentid,ismxb=:new.ismxb,isjsb=:new.isjsb,standard=:new.standardwhere LEECHDOMID=:old.leechdomid;end;-create or replace trigger th_deleteafter delete on SNM_ITEMID for each row begin delete from basedata_th
9、erapyitemwhere 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,memoryco
10、de)values(:new.ITEMID,:new.itemcode,:new.name,: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 eac
11、h row begin update basedata_therapyitemset itemid = :new.itemid,itemcode = :new.itemcode,name = :new.name,unit = :new.unit,feesort = :new.feesort,feesortname = :new.feesortname,paymethod = :new.paymethod,paymethodname = :new.paymethodname,exceptioncont = :new.exceptioncont,description = :new.descrip
12、tion,meaning = :new.meaning,itemsort = :new.itemsort,isitem = :new.isitem,parentid = :new.parentid,memorycode = :new.memorycodewhere itemid = :old.itemid;end;-测试-查看快照 -SNM_LEECHDOMID 和 SNM_ITEMID 运行状况-在目的数据库中执行 -SELECT NAME,LAST_REFRESH FROM ALL_SNAPSHOT_REFRESH_TIMES;select last_date,next_date,what from user_jobs order by next_date;-测试查询 源数据库数据-在目的数据库中执行-select * from basedata_leechdomdblink_orcl_bql_scan;
