创建表空间
创建临时表空间=====================================
create temporary tablespace test_temp tempfile 'e:\oracle\oradata\cdctest\test_temp01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local;
创建数据表空间======================================
create tablespace test_data logging datafile 'e:\oracle\oradata\cdctest\test_data01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local;
创建用户
1)业务操作用户
创建业务操作用户并指定表空间=========================
create user appuser identified by appuser default tablespace test_data temporary tablespace test_temp;
给用户授予权限======================================
grant connect,resource, create view to appuser
2)发布用户
创建发布用户并指定表空间=============================
create user cdc_pub identified by cdc default tablespace test_data temporary tablespace test_temp;
给用户授予权限=======================================
grant connect,resource to cdc_pub -- connect 连接权限,resource用于给开发人员用的角色 grant SELECT_CATALOG_ROLE TO cdc_pub --可以查看一些数据字典的视图· GRANT EXECUTE_CATALOG_ROLE TO cdc_pub--执行目录角色,能够执行所有系统包 GRANT EXECUTE ON DBMS_CDC_PUBLISH TO cdc_pub--用于定义发布操作 grant create job to cdc_pub --创建任务
3)订阅用户
创建订阅用户并指定表空间=============================
create user cdc_sub identified by cdc default tablespace test_data temporary tablespace test_temp;
给用户授予权限=======================================
grant connect,resource to cdc_sub -- connect 连接权限,resource用于给开发人员用的角色 grant execute on DBMS_CDC_SUBSCRIBE TO CDC_SUB --用于定义订阅操作
2.创建业务表:以业务用户账户(APPUSER)登录
create table SalesOrder( orderId int not null, customerId int not null, duedate date not null, deliverTo int not null, createddttm date default sysdate, constraint pk_salesOrder primary key (orderId) ) select * from salesorder create table salesorderdetail( solineId int not null, orderId int not null, itemNumber varchar2(20) not null, quantity decimal(13,4), linePrice decimal(13,4), constraint pk_sodetail primary key (solineId) ) select * from salesorderdetail
3.创建发布:以发布者登录(CDC_PUB)
1)创建发布集
BEGIN DBMS_CDC_PUBLISH.CREATE_CHANGE_SET( change_set_name => 'CDC_SET_SO', --改变集 description => 'Change set for SalesOrder, SalesOrderDetail', change_source_name => 'SYNC_SOURCE'); END;
2)创建发布表:一个发布集对应多个发布的表
发布表即是用于存放变更了的数据的表。以下语句将在发布者(CDC_PUB)名下新建两个发布表:CT_SALESORDER和CT_SALESORDERDETAIL。
BEGIN DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE( DDL_MARKERS=>'n', owner => 'cdc_pub', --发布表的Owner! change_table_name => 'CT_SalesOrder', --发布表名 change_set_name => 'CDC_SET_SO', --改变集 source_schema => 'appuser', --业务表的Owner source_table => 'SalesOrder', --业务表 column_type_list => 'OrderID int, CustomerID int, DueDate Date, DeliverTo int, 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; BEGIN DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE( DDL_MARKERS=>'n', owner => 'cdc_pub', change_table_name => 'CT_SalesOrderDetail', change_set_name => 'CDC_SET_SO', source_schema => 'appuser', source_table => 'SalesOrderDetail', column_type_list => 'SOLineID int, OrderID int, ItemNumber varchar2(20), Quantity decimal(13,4), LinePrice decimal(18,4)', 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;
3)给订阅用户授权,使其对发布表有读权限
grant select on CT_SALESORDER to cdc_sub grant select on CT_SalesOrderDetail to cdc_sub
4.创建订阅:以订阅者(CDC_SUB)登录
1)创建订阅:一个订阅中可订阅多个发布表
BEGIN DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION( change_set_name => 'CDC_SET_SO', --改变集 description => 'Change data for salesOrder, salesOrderDetail', subscription_name => 'CDC_SUB_SO'); --订阅的名称 END;
2)订阅表:系统将针对每个发布表建立订阅视图,将来订阅时从这些视图读取数据
begin DBMS_CDC_SUBSCRIBE.SUBSCRIBE( subscription_name => 'CDC_SUB_SO', --订阅的名称 source_schema => 'APPUSER', --业务数据表的Owner source_table => 'SALESORDER', --业务数据表名 --订阅的列 column_list => 'OrderID, CustomerID, DueDate, DeliverTo, CreateDTTM', --订阅试图的名称 subscriber_view => 'V_CDC_SalesOrder'); END; begin DBMS_CDC_SUBSCRIBE.SUBSCRIBE( subscription_name => 'CDC_SUB_SO', source_schema => 'APPUSER', source_table => 'SALESORDERDetail', column_list => 'SOLINEID,ORDERID,ITEMNUMBER,QUANTITY,LINEPRICE', subscriber_view => 'V_CDC_SalesOrderDetail'); END;
5.激活订阅
BEGIN DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION( subscription_name => 'CDC_SUB_SO'); END;
至此,发布、订阅的管理工作完成。以下是进行测试。
可以看出,Oracle 11g的同步CDC不是基于触发器的!从ALL_TRIGGERS找不到业务表上有触发器。
6.操作业务表:用业务用户(APPUSER)登录
insert into SalesOrder ( orderId, customerId, dueDate, deliverTo ) values (1, 1, trunc(sysdate)+10, 1) insert into SalesOrderDetail( SoLineId, OrderId, ItemNumber, Quantity, linePrice) values ( 1, 1, 'Desk001', 2, 500) insert into SalesOrderDetail( SoLineId, OrderId, ItemNumber, Quantity, linePrice) values ( 2, 1, 'Chair001', 2, 350) --注意,可以试一下,在没有提交之前,在发布表中是没有数据的。这一点似乎也和Oracle 10g不同。 commit
7.测试订阅:以订阅用户(CDC_SUB)登录
begin dbms_cdc_subscribe.extend_window( subscription_name=>'CDC_SUB_SO'); --订阅名 end; -- 查询订阅视图 select * from V_CDC_SalesOrder order by commit_timestamp$ select * from V_CDC_SalesOrderDetail order by commit_timestamp$ --完成本次订阅 begin DBMS_CDC_SUBSCRIBE.PURGE_WINDOW( subscription_name => 'CDC_SUB_SO'); --订阅名 END;
oracle 文档 ——