oracle11g cdc

create studenttest(id number not null primary key ,name varchar2(10),sex varchar2(2));

ALTER system set job_queue_processes=14

alter SYSTEM set java_pool_size =50m


create tablespace ts_cdcpub datafile '/u01/oradata/doea5dw/ts_cdcpub.dbf' size 100m;
--1. 订阅用户和发布用户
create user cdc_publisher identified by cdc_publisher default tablespace ts_cdcpub;
create user cdc_subcriber identified by cdc_subcriber default tablespace ts_cdcpub;
--2.给用户授予权限
GRANT CREATE SESSION to cdc_publisher;
GRANT CREATE TABLE to cdc_publisher;
GRANT CREATE TABLESPACE to cdc_publisher;
GRANT UNLIMITED TABLESPACE to cdc_publisher;
GRANT SELECT_CATALOG_ROLE to cdc_publisher;
GRANT EXECUTE_CATALOG_ROLE to cdc_publisher;
GRANT EXECUTE ON DBMS_CDC_PUBLISH TO cdc_publisher;
grant connect,resource to cdc_publisher;
GRANT CREATE job to cdc_publisher;

grant connect,resource to cdc_subcriber;
grant execute on dbms_cdc_subscribe to cdc_subcriber;

grant all on studenttest to cdc_publisher;
grant all on studenttest to cdc_subcriber;

--用发布者身份登录 创建发布集
begin
  Dbms_Cdc_Publish.create_change_set(change_set_name => 'student_change_set',description => 'change test for cdc',change_source_name => 'SYNC_SOURCE');
  end;
 --创建发布表：-个发布集对应多个发布表
BEGIN
  DBMS_CDC_PUBLISH.CREATE_CHANGE_TABLE(
  owner => 'cdc_publisher',
  change_table_name => 'student_ct',
  change_set_name=>'student_change_set',
  source_schema => 'scott',
  source_table => 'STUDENTTEST',
  column_type_list => 'ID number,NAME VARCHAR2(10),SEX varchar2(2)',
  capture_values => 'new',
  rs_id => 'y',
  row_id => 'n',
  user_id => 'n',
  timestamp => 'n',
  object_id => 'n',
  ddl_markers => 'n',--11g new feature
  source_colmap => 'y',
  target_colmap => 'y',
  options_string => 'TABLESPACE ts_cdcpub');
  END;
 

grant select on cdc_publisher.student_ct to cdc_subcriber;

select change_set_name,pub_id,source_table_name from all_published_columns;

-- 以创建者登录 创建订阅者
begin
  dbms_cdc_subscribe.create_subscription
  (
  change_set_name =>'student_change_set', description=>'student test', subscription_name =>'student_sub'
  );
  END;
--创建订阅表 一个订阅者中有多个订阅表
BEGIN
  dbms_cdc_subscribe.subscribe
  (subscription_name =>'student_sub',
  source_schema=>'scott',
  source_table=>'studenttest',
  column_list=>'id,name,sex',
  subscriber_view=>'student_view');
  end;
 
   --激活订阅
  BEGIN
    dbms_cdc_subscribe.activate_subscription(subscription_name => 'student_sub');
    end;
    
    --测试
    
    update studenttest set name = '阿三' where id =1 ;
    commit;
    
  --测试订阅
  begin
    dbms_cdc_subscribe.extend_window(
    subscription_name => 'student_sub');
    end;
    
        select * FROM student_view
    
    
    --完成订阅
    begin
      dbms_cdc_subscribe.purge_window
      ( subscription_name =>'student_sub');
      end;