ORACLE 11g 同步CDC实验
- 创建数据库用户：以管理员账户登录
- 业务操作用户
create user appuser identified by appuser default tablespace users;
grant connect, resource to appuser
grant create view to appuser
- 发布用户
create user cdc_pub identified by cdc default tablespace users;
grant connect, resource to cdc_pub
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 -- Oracle 11g 中为必须
- 订阅用户
create user cdc_sub identified by cdc default tablespace users;
grant connect, resource to cdc_sub
grant execute on DBMS_CDC_SUBSCRIBE TO CDC_SUB
- 创建业务表：以业务用户账户(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) )
create table SalesOrderDetail ( SOLineId int not null,
orderID int not null, itemNumber varchar2(20) not null,
quantity decimal(13,4), linePrice decimal(13,4)w,
constraint pk_SODetail primary key ( SoLineID ) )
- 创建发布：以发布者登录(CDC_PUB)
- 创建发布集
BEGIN
DBMS_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。
BEGIN
DBMS_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', --业务表的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 => 'CDCSET_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;
- 给订阅用户授权，使其对发布表有读权限
grant select on CT_SALESORDER to cdc_sub
grant select on CT_SalesOrderDetail to cdc_sub
- 创建订阅：以订阅者(CDC_SUB)登录
- 创建订阅：一个订阅中可订阅多个发布表
BEGIN
DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(
change_set_name => 'CDCSET_SO', --改变集
description => 'Change data for salesOrder, salesOrderDetail',
subscription_name => 'CDCSUB_SO'); --订阅的名称
END;
- 订阅表：系统将针对每个发布表建立订阅视图，将来订阅时从这些视图读取数据
begin
DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
subscription_name => 'CDCSUB_SO', --订阅的名称
source_schema => 'APPUSER', --业务数据表的Owner
source_table => 'SALESORDER', --业务数据表名
--订阅的列
column_list => 'ORDERID,CUSTOMERID,DUEDATE,DELIVERTO,CREATEDDTTM',
--订阅试图的名称
subscriber_view => 'V_CDC_SalesOrder');
END;
begin
DBMS_CDC_SUBSCRIBE.SUBSCRIBE(
subscription_name => 'CDCSUB_SO',
source_schema => 'APPUSER',
source_table => 'SALESORDERDetail',
column_list => 'SOLINEID,ORDERID,ITEMNUMBER,QUANTITY,LINEPRICE',
subscriber_view => 'V_CDC_SalesOrderDetail');
END;
- 激活订阅
BEGIN
DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(
subscription_name => 'CDCSUB_SO');
END;
至此，发布、订阅的管理工作完成。以下是进行测试。
可以看出，Oracle 11g的同步CDC不是基于触发器的！从ALL_TRIGGERS找不到业务表上有触发器。
- 操作业务表：用业务用户(APP_USER)登录
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
- 测试订阅：以订阅用户(CDC_SUB)登录
begin
dbms_cdc_subscribe.extend_window(
subscription_name=>'CDCSUB_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 => 'CDCSUB_SO'); --订阅名
END;
