CDC(Change Data Capture)牛刀小试

环境:

OS:Red Hat Linux As 5

DB:11.1.0.6

Oracle提供了DBMS_CDC_PUBLISH和DBMS_CDC_SUBSCRIBE这两个包,通过这两个包,我们可以捕获到表的DML操作,下面是一个测试例子.

 

1.创建发布用户(sys或具有dba权限的用户下执行)
create user cdc_pub identified by hxl default tablespace users;

 

2.授予相应权限(sys用户下执行)
grant create session to cdc_pub;
grant create table to cdc_pub;
grant create tablespace to cdc_pub;
grant unlimited tablespace 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;

 

3.在scott用户下创建测试表并写入记录(scott下执行)
SQL> Create Table person (id Number,Name Varchar(10),*** Varchar(2));
Table created
SQL> insert into person values(0,'毛泽东','男');
1 row inserted
SQL> insert into person values(0,'彭德怀','男');
1 row inserted
SQL> insert into person values(0,'邓颖操','女');
1 row inserted
SQL> commit;
Commit complete
SQL> grant all on person to cdc_pub;
Grant succeeded

 

4.发布数据(在cdc_pub schema下执行)
BEGIN
  DBMS_CDC_PUBLISH.CREATE_CHANGE_SET(change_set_name    => 'person_change_set', --改变集
                                     description        => 'Change set for person info',
                                     change_source_name => 'SYNC_SOURCE');
END;

 

5.创建改变表(在cdc_pub schema下执行)
BEGIN
  DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(owner             => 'cdc_pub',
                                       change_table_name => 'person_ct',
                                       change_set_name   => 'person_change_set',
                                       source_schema     => 'scott',
                                       source_table      => 'person',
                                       column_type_list  => 'ID number,NAME VARCHAR2(10),*** varchar2(2)',
                                       capture_values    => 'both',
                                       rs_id             => 'y',
                                       row_id            => 'n',
                                       user_id           => 'n',
                                       timestamp         => 'n',
                                       object_id         => 'n',
                                       source_colmap     => 'y',
                                       target_colmap     => 'y',
                                       options_string    => 'TABLESPACE users',
                                       ddl_markers       => 'n'); -- 10g没有这个参数
END;

 

6.订阅数据(在cdc_pub schema下执行)
BEGIN
  DBMS_CDC_SUBSCRIBE.CREATE_SUBSCRIPTION(change_set_name   => 'PERSON_CHANGE_SET',
                                         description       => 'Change data for person',
                                         subscription_name => 'PERSON_SUB');
END;

 

7.订阅表(在cdc_pub schema下执行)
BEGIN
  DBMS_CDC_SUBSCRIBE.SUBSCRIBE(subscription_name => 'PERSON_SUB',
                               source_schema     => 'scott',
                               source_table      => 'person',
                               column_list       => 'id,name,***',
                               subscriber_view   => 'PERSON_VIEW');
END;

 

8.激活订阅(在cdc_pub schema下执行)
Begin
  dbms_cdc_subscribe.activate_subscription(subscription_name => 'PERSON_SUB');
END;

 

9.扩展窗口的应用(查看增量的数据)
 BEGIN
   DBMS_CDC_SUBSCRIBE.EXTEND_WINDOW(subscription_name => 'PERSON_SUB');
 END;

 

10.测试及查看改变数据(scott下执行)
SQL> select * from person;
        ID NAME       ***
---------- ---------- ---
         0 毛泽东     男
         0 彭德怀     男
         0 邓颖操     女

SQL> insert into person values('1','刘少奇','男');

1 row inserted

SQL> update person set name='毛主席' where name='毛泽东';

1 row updated

SQL> delete from person where name ='刘少奇';

1 row deleted

SQL> commit;

Commit complete

 

11.捕获的改变数据(在cdc_pub schema下执行)

begin
   dbms_cdc_subscribe.extend_window('PERSON_SUB');
end;

 

SQL>select OPERATION$, CSCN$, COMMIT_TIMESTAMP$,   RSID$, "ID", "NAME", "***" from person_view;
OPERATION$ CSCN$ COMMIT_TIMESTAMP$ RSID$ ID NAME ***
I  935979 2012-7-17 11:38:32 1 1 刘少奇 男
UU 935979 2012-7-17 11:38:32 2 0 毛泽东 男
UN 935979 2012-7-17 11:38:32 2 0 毛主席 男
D  935979 2012-7-17 11:38:32 3 1 刘少奇 男

 

从以上信息可以很清楚到看到insert/update/delete的数据.

说明:

使用CDC的时候,针对大数据量的操作时间比没有使用CDC至少多一倍的时间,因为CDC需要向改变表(change_table)写入操作的日志.

 

-- The End --