搜索
实现方案在本地建立两张表(wcmmetatablebook_baseinfo,wcmmetatablebook_extinfo)，这两张表和远程的表结构一样，通过触发器实现数据的同步，然后对本地的两张表进行物化，再在物化视图上建立触发器，实时的修改bookexpinfo表(并没有考虑大字段的情况)。该方案是测试成功的，源数据库是oracle10.1.0.2.0,目标数据库是oracle9.2.0.1.0,并且做到了数据的实时更新。实现方案如下图所示：wcmmetatablebook_baseinfo、wcmmetatablebook_extinfowcmmetatablebook_baseinfo、wcmmetatablebook_extinfo触发器视图Mv_bookepxinfo表bookexpinfo物化触发器具体实现1、在源数据库创建databaselink,确保两台服务器可以连通。首先在oracle的E:\oracle\ora92\network\admin配置文件里添加远程oracle服务器的实例，具体如下：CCPPH160=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.15.160)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)))然后执行创建databaselink的语句：createdatabaselinkTEST160.US.ORACLE.COMconnecttoCCPPH1identifiedbyCCPPH1using'CCPPH160';然后测试是否已连通，在命令窗口输入：Selectcount(*)fromwcmmetatablebook_baseinfo@test160;效果如下图所示：这个时候会看到已经可以正常查询出数据了。2、在本地建表(wcmmetatablebook_baseinfo,wcmmetatablebook_extinfo),要和源数据库中的表结构一致。--图书基本信息表createtablewcmmetatablebook_baseinfoasselect*fromwcmmetatablebook_baseinfo@test160;altertableWCMMETATABLEBOOK_BASEINFOaddconstraintPK_WCMMETATABLEBOOK_BASEINFOIDprimarykey(WCMMETATABLEBOOK_BASEINFOID);createuniqueindexUNIQUE1_RESOURCEUNIQUEIDonWCMMETATABLEBOOK_BASEINFO(RESOURCEUNIQUEID);--图书扩展信息表createtablewcmmetatablebook_extinfoasselect*fromwcmmetatablebook_extinfo@test160;altertableWCMMETATABLEBOOK_EXTINFOaddconstraintPK_WCMMETATABLEBOOK_EXTINFOIDprimarykey(WCMMETATABLEBOOK_EXTINFOID);createuniqueindexUNIQUE2_RESOURCEUNIQUEIDonWCMMETATABLEBOOK_EXTINFO(RESOURCEUNIQUEID);3、在源数据库上分别建立触发器--图书基本表触发器createorreplacetriggerTRI_WCMMETATABLEBOOK_BASEINFOafterinsertorupdateordeleteonWCMMETATABLEBOOK_BASEINFOforeachrowbeginifdeletingthendbms_output.put_line('删除');deletefromWCMMETATABLEBOOK_BASEINFO@test164whereWCMMETATABLEBOOK_BASEINFOID=:old.WCMMETATABLEBOOK_BASEINFOID;endif;ifinsertingthendbms_output.put_line('插入');insertintoWCMMETATABLEBOOK_BASEINFO@test164(WCMMETATABLEBOOK_BASEINFOID,METADATAID,CHANNELID,CRUSER,CRTIME,CREATOR,ORIGINALTITLE,LANGUAGE,PRINTNO,PRINTCOUNT,RESOURCEUNIQUEID,COVER,TITLE,DESCRIPTION,TYPE,FORMAT,TRANSLATOR,CONTRIBUTOR,PUBLISHER,PUBDATE,PUBCOUNT,PUBNO,ISBN,RELATEDBOOKS,RELATEDARTICLES,RELATEDVIDEOS,DYNASTY,QUOTATIONCONTENT,PRICE,FRAMETYPE,ISOVERSEAS,SOURCELANGUAGE,PUBDESCRIPTION,PAGECOUNT,PRINTCOUNTYEAR,PRINTCOUNTMONTH,FORMATSIZE1,FORMATSIZE2,DUTYEDITOR,TAG,PRIZEINFO,PRINTFLOAT,RELATEDSERIES,RELATEDSETS,RELATEDAUDIO,ISAUDIO,ISVIDEO,ISREVISE,WORDCOUNT,OTHERPUBLISHER,OTHERLANGUAGE,KIND,ISSERIES,ISSETS,SERIESNAME,SETSNAME,SERIESFASCICULE,SETSFASCICULE1,SERIESORDER,SETSORDER,ISPLAN,EXPUBLISHER,DAYBOOK,MAINKEYWORD,CATALOG_PIC,CONTENT_PIC,BOOKCONTENT,SERIES_PIC,AUTHORINTRODUCE)values(:new.WCMMETATABLEBOOK_BASEINFOID,:new.METADATAID,:new.CHANNELID,:new.CRUSER,:new.CRTIME,:new.CREATOR,:new.ORIGINALTITLE,:new.LANGUAGE,:new.PRINTNO,:new.PRINTCOUNT,:new.RESOURCEUNIQUEID,:new.COVER,:new.TITLE,:new.DESCRIPTION,:new.TYPE,:new.FORMAT,:new.TRANSLATOR,:new.CONTRIBUTOR,:new.PUBLISHER,:new.PUBDATE,:new.PUBCOUNT,:new.PUBNO,:new.ISBN,:new.RELATEDBOOKS,:new.RELATEDARTICLES,:new.RELATEDVIDEOS,:new.DYNASTY,:new.QUOTATIONCONTENT,:new.PRICE,:new.FRAMETYPE,:new.ISOVERSEAS,:new.SOURCELANGUAGE,:new.PUBDESCRIPTION,:new.PAGECOUNT,:new.PRINTCOUNTYEAR,:new.PRINTCOUNTMONTH,:new.FORMATSIZE1,:new.FORMATSIZE2,:new.DUTYEDITOR,:new.TAG,:new.PRIZEINFO,:new.PRINTFLOAT,:new.RELATEDSERIES,:new.RELATEDSETS,:new.RELATEDAUDIO,:new.ISAUDIO,:new.ISVIDEO,:new.ISREVISE,:new.WORDCOUNT,:new.OTHERPUBLISHER,:new.OTHERLANGUAGE,:new.KIND,:new.ISSERIES,:new.ISSETS,:new.SERIESNAME,:new.SETSNAME,:new.SERIESFASCICULE,:new.SETSFASCICULE1,:new.SERIESORDER,:new.SETSORDER,:new.ISPLAN,:new.EXPUBLISHER,:new.DAYBOOK,:new.MAINKEYWORD,:new.CATALOG_PIC,:new.CONTENT_PIC,:new.BOOKCONTENT,:new.SERIES_PIC,:new.AUTHORINTRODUCE);endif;ifupdatingthendbms_output.put_line('修改');updateWCMMETATABLEBOOK_BASEINFO@test164setWCMMETATABLEBOOK_BASEINFOID=:new.WCMMETATABLEBOOK_BASEINFOID,METADATAID=:new.METADATAID,CHANNELID=:new.CHANNELID,CRUSER=:new.CRUSER,CRTIME=:new.CRTIME,CREATOR=:new.CREATOR,ORIGINALTITLE=:new.ORIGINALTITLE,LANGUAGE=:new.LANGUAGE,PRINTNO=:new.PRINTNO,PRINTCOUNT=:new.PRINTCOUNT,RESOURCEUNIQUEID=:new.RESOURCEUNIQUEID,COVER=:new.COVER,TITLE=:new.TITLE,DESCRIPTION=:new.DESCRIPTION,TYPE=:new.TYPE,FORMAT=:new.FORMAT,TRANSLATOR=:new.TRANSLATOR,CONTRIBUTOR=:new.CONTRIBUTOR,PUBLISHER=:new.PUBLISHER,PUBDATE=:new.PUBDATE,PUBCOUNT=:new.PUBCOUNT,PUBNO=:new.PUBNO,ISBN=:new.ISBN,RELATEDBOOKS=:new.RELATEDBOOKS,RELATEDARTICLES=:new.RELATEDARTICLES,RELATEDVIDEOS=:new.RELATEDVIDEOS,DYNASTY=:new.DYNASTY,QUOTATIONCONTENT=:new.QUOTATIONCONTENT,PRICE=:new.PRICE,FRAMETYPE=:new.FRAMETYPE,ISOVERSEAS=:new.ISOVERSEAS,SOURCELANGUAGE=:new.SOURCELANGUAGE,PUBDESCRIPTION=:new.PUBDESCRIPTION,PAGECOUNT=:new.PAGECOUNT,PRINTCOUNTYEAR=:new.PRINTCOUNTYEAR,PRINTCOUNTMONTH=:new.PRINTCOUNTMONTH,FORMATSIZE1=:new.FORMATSIZE1,FORMATSIZE2=:new.FORMATSIZE2,DUTYEDITOR=:new.DUTYEDITOR,TAG=:new.TAG,PRIZEINFO=:new.PRIZEINFO,PRINTFLOAT=:new.PRINTFLOAT,RELATEDSERIES=:new.RELATEDSERIES,RELATEDSETS=:new.RELATEDSETS,RELATE