Oracle CDC incremental test code instance, oraclecdc

Oracle CDC incremental test code instance, oraclecdc

Create a tablespace

Create a temporary tablespace =================================================

create temporary tablespace test_temptempfile 'e:\oracle\oradata\cdctest\test_temp01.dbf'size 32mautoextend onnext 32m maxsize 2048mextent management local;

Create a data table =

create tablespace test_dataloggingdatafile 'e:\oracle\oradata\cdctest\test_data01.dbf'size 32mautoextend onnext 32m maxsize 2048mextent management local;

Create user

1) Business Operation users

Create a business operation user and specify the tablespace ========================

create user appuser identified by appuser default tablespace test_datatemporary tablespace test_temp;

Grant permissions to a user ==================================================== =

grant connect,resource, create view to appuser

2) Publish users

Create a publishing user and specify the tablespace ================================

create user cdc_pub identified by cdc default tablespace test_datatemporary tablespace test_temp;

Grant permissions to a user ==================================================== ===

Grant connect, resource to cdc_pub -- connect connection permission, resource is used by developers TO grant SELECT_CATALOG_ROLE TO cdc_pub. You can view some data dictionary views. GRANT EXECUTE_CATALOG_ROLE TO cdc_pub. Execute directory roles, can EXECUTE all system packages grant execute on DBMS_CDC_PUBLISH TO cdc_pub -- used to define the publishing operation grant create job TO cdc_pub -- create a task

3) Subscriber

Create a subscriber and specify the tablespace ================================

create user cdc_sub identified by cdc default tablespace test_datatemporary tablespace test_temp;

Grant permissions to a user ==================================================== ===

Grant connect, resource to cdc_sub -- connect connection permission. resource is used TO grant the developer the role grant execute on DBMS_CDC_SUBSCRIBE to CDC_SUB -- used TO define subscription operations.

2. Create a business table: Log On with the Business User Account (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 salesordercreate 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. Create a release: log on to the publisher (CDC_PUB)

1) create a release set

BEGIN DBMS_CDC_PUBLISH.CREATE_CHANGE_SET (change_set_name => 'cdc _ SET_SO ', -- Change set description => 'change set for SalesOrder, SalesOrderDetail', change_source_name => 'sync _ source'); END;

2) create a publishing table: One publishing set corresponds to multiple publishing tables.

A publishing table is a table used to store changed data. The following statement creates two release tables under the publisher (CDC_PUB): CT_SALESORDER and CT_SALESORDERDETAIL.

BEGIN DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE (DDL_MARKERS => 'n', owner => 'cdc _ pub', -- the Owner of the publishing table! Change_table_name => 'ct _ SalesOrder ', -- publication table name change_set_name => 'cdc _ SET_SO', -- change set source_schema => 'appuser ', -- business table Owner source_table => 'salesorder', -- business table column_type_list => 'orderid int, mermerid int, DueDate Date, DeliverTo int, CreateDTTM date ', -- The column definitions in the publishing table are capture_values => 'new', -- Obtain the changed value 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 () ', 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) authorize the subscriber to read the publishing table.

grant select on CT_SALESORDER to cdc_subgrant select on CT_SalesOrderDetail to cdc_sub

4. Create subscription: Log On As A subscriber (CDC_SUB)

1) Create subscription: one subscription can subscribe to multiple publishing tables.

BEGIN DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION (change_set_name => 'cdc _ SET_SO ', -- Change set description => 'change data for salesOrder, salesOrderDetail', subscription_name => 'cdc _ SUB_SO '); -- subscription name END;

2) Subscription table: the system creates a subscription view for each publishing table and reads data from these views during future subscription.

Begin DBMS_CDC_SUBSCRIBE.SUBSCRIBE (subscription_name => 'cdc _ SUB_SO ', -- subscription name source_schema => 'appuser', -- business data table Owner source_table => 'salesorder ', -- business data table name -- subscribed column_list => 'orderid, mermerid, DueDate, DeliverTo, CreateDTTM ', -- Name of the subscription attempt subscriber_view => 'v _ CDC_SalesOrder'); END; begin partition (subscription_name => 'cdc _ SUB_SO ', source_schema => 'appuser', source_table => 'salesorderdetail', column_list => 'solineid, ORDERID, ITEMNUMBER, QUANTITY, linepric', subscriber_view => 'v _ CDC_SalesOrderDetail '); END;

5. Activate subscription

BEGIN    DBMS_CDC_SUBSCRIBE.ACTIVATE_SUBSCRIPTION(        subscription_name => 'CDC_SUB_SO');END;

Now, the management of publishing and subscription is complete. The following is a test.

We can see that Oracle 11g synchronization CDC is not based on triggers! No triggers are found in the business table from ALL_TRIGGERS.

6. operate the business table: log on with the business user (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, '000000', 2,500) insert into SalesOrderDetail (SoLineId, OrderId, ItemNumber, Quantity, linePrice) values (2, 1, 'chair001', 2,350) -- Note: You can try it. There is no data in the release table before submission. This seems to be different from Oracle 10 Gb. Commit

7. Test subscription: Log On As A subscription user (CDC_SUB)

Begin dbms_cdc_subscribe.extend_window (subscription_name => 'cdc _ SUB_SO '); -- subscription name end; -- query the subscription view select * from V_CDC_SalesOrder order by commit_timestamp $ select * from V_CDC_SalesOrderDetail order by commit_timestamp $ -- complete the subscription (subscription_name => 'cdc _ SUB_SO '); -- subscription name END;

Oracle document --