资源描述：
《oracle数据库同步dblink》由会员上传分享，免费在线阅读，更多相关内容在行业资料-天天文库。
1、--------------下面两种建立dblink方法人选一种---------------------远程连接数据流-------1、---------目的数据库创建dblink------------------------------------------------------------------CREATEPUBLICDATABASELINK数据库链名称CONNECTTO用户名identifiedby密码using'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=远程服务器IP)(PORT=端口)))(CONN
2、ECT_DATA=(service_name=连接到数据库服务名)))';例：CREATEPUBLICDATABASELINKdblink_orcl_bql_scanCONNECTTOemuser_bqlidentifiedbyempsw_bqlusing'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=172.17.193.161)(PORT=1521)))(CONNECT_DATA=(service_name=orcl)))';-------2、----------目的数据库创建dblinkdroppublicdatabas
3、elinkdblink_orcl_bql_scan;CreatepublicDATABASELINKdblink_orcl_bql_scanCONNECTTOemuser_bqlIDENTIFIEDBYemuser_bqlUSING'orcl';----------------在源数据库上，创建要同步表的快照日志 Createsnapshotlogonbasedata_leechdom;Createsnapshotlogonbasedata_therapyitem;-------第二步---------------在目的数据库-------------建立快照--------------
4、CreatesnapshotSNM_LEECHDOMIDasselect*frombasedata_leechdom@dblink_orcl_bql_scan; CreatesnapshotSNM_ITEMIDasselect*frombasedata_therapyitem@dblink_orcl_bql_scan;AltersnapshotSNM_LEECHDOMIDrefreshfastStartwithsysdatenextsysdatewithprimarykey;AltersnapshotSNM_ITEMIDrefreshfastStartwithsysdatenextsysda
5、tewithprimarykey;-------------------------在目的数据库建立触发器-------注：单个执行-----------createorreplacetriggersn_deleteafterdeleteonSNM_LEECHDOMIDforeachrowbegindeletefrombasedata_leechdomwhereLEECHDOMID=:old.leechdomid;end;------------------------------------------------------------------------createorreplac
6、etriggersn_insertafterinsertonSNM_LEECHDOMIDforeachrowbegininsertintobasedata_leechdom(leechdomid,leechdomcode,leechdomname,feesort,feesortname,paymethod,paymethodname,type,scope,memorycode,isleechdom,parentid,ismxb,isjsb,standard) values(:new.leechdomid,:new.leechdomcode,:new.leechdomname,:new.fe
7、esort,:new.feesortname,:new.paymethod,:new.paymethodname,:new.type,:new.scope,:new.memorycode,:new.isleechdom,:new.parentid,:new.ismxb,:new.isjsb,:new.standard);end;-----------------------------------------
