ORACLE 11g同步CDC实验.docx

ORACLE 11g 同步CDC实验创建数据库用户：以管理员账户登录业务操作用户createuserappuseridentifiedbyappuserdefaulttablespaceusers;grantconnect,resourcetoappusergrantcreateviewtoappuser发布用户createusercdc_pubidentifiedbycdcdefaulttablespaceusers;grantconnect,resourcetocdc_pubGRANT SELECT_CATALOG_ROLE TOcdc_pubGRANT EXECUTE_CATALOG_ROLE TOcdc_pubGRANTEXECUTEONDBMS_CDC_PUBLISHTOcdc_pubgrantcreatejobtocdc_pub -- Oracle 11g 中为必须订阅用户createusercdc_subidentifiedbycdcdefaulttablespaceusers;grantconnect,resourcetocdc_subgrantexecuteonDBMS_CDC_SUBSCRIBETO CDC_SUB创建业务表：以业务用户账户(APPUSER)登录createtableSalesOrder(orderIdintnotnull,customerIdintnotnull,DueDatedatenotnull,deliverTointnotnull,createddttmdatedefaultsysdate,constraintpk_salesOrderprimarykey(orderId))createtableSalesOrderDetail(SOLineIdintnotnull,orderIDintnotnull,itemNumbervarchar2(20)notnull, quantity decimal(13,4),linePricedecimal(13,4)w,constraintpk_SODetailprimarykey(SoLineID))创建发布：以发布者登录(CDC_PUB)创建发布集BEGINDBMS_CDC_PUBLISH.CREATE_CHANGE_SET(change_set_name=CDCSET_SO,--改变集 description =Change set for SalesOrder, SalesOrderDetail,change_source_name=SYNC_SOURCE);END;创建发布表：一个发布集对应多个发布的表发布表即是用于存放变更了的数据的表。以下语句将在发布者（CDC_PUB）名下新建两个发布表：CT_SALESORDER和CT_SALESORDERDETAIL。BEGINDBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE( DDL_MARKERS=n, owner =cdc_pub,--发布表的Owner！change_table_name=CT_SalesOrder,--发布表名change_set_name=CDCSET_SO,--改变集source_schema=appuser,--业务表的Ownersource_table=SalesOrder,--业务表column_type_list=OrderIDint, CustomerIDint,DueDate Date, DeliverToint,CreateDTTM Date, --发布表中的列定义capture_values=new, -- rs_id=n,row_id=n,user_id=n,timestamp=n,object_id=n,source_colmap=n,target_colmap=n,options_string=null);END;BEGINDBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE( DDL_MARKERS=n, owner =cdc_pub,change_table_name=CT_SalesOrderDetail,change_set_name=CDCSET_SO,source_schema=appuser,source_table=SalesOrderDetail,column_type_list=SOLineIDint, OrderIDint,ItemNumbervarchar2(20), Quantity decimal(13,4), LinePrice decimal(18,4),capture_values=new,rs_id=n,row_id=n,user_id=n