Oracle CDC数据增量测试代码实例

创建表空间

创建临时表空间=====================================

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 文档 ——