--在關於Change Data Capture（一）中介紹了CDC的一些基本概念和類型。這篇文章主要是通過一個實際的例子來示範實現同步模式的CDC的基本步驟。
-- Create table
create table SALES
(
ID NUMBER,
PRODUCTID NUMBER,
PRICE NUMBER,
QUANTITY NUMBER
)
--url:http://www.ningoo.net/html/tag/cdc
--2.1.首先在source database建立一個使用者作為發行者
create user cdcpub identified by cdcpub;
--2.2.授予相應的許可權
grant execute_catalog_role to cdcpub;
grant select_catalog_role to cdcpub;
grant create table to cdcpub;
grant create session to cdcpub;
grant dba to cdcpub;
grant execute on dbms_cdc_publish to cdcpub; --出錯
--3.1
alter system set java_pool_size=48M;
--4.1
grant all on system.sales to cdcpub; --出錯
--4.2
begin
dbms_cdc_publish.create_change_set(
change_set_name =>'test_cdc',
description =>'change set for ning.sales',
change_source_name =>'SYNC_SOURCE');
end;
--4.3
begin
dbms_cdc_publish.create_change_table(
owner =>'cdcpub',
change_table_name=>'sales_ct',
change_set_name=>'test_cdc',
source_schema=>'system',
source_table=>'sales',
column_type_list=>'id int,productid int,price number(10,2),quantity int',
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');--Oracle11 新添加參數
end;
--5.1
create user cdcsub identified by cdcsub;
grant create session to cdcsub;
grant create table to cdcsub;
grant select on cdcpub.sales_ct to cdcsub;
--5.3.建立訂閱
begin
dbms_cdc_subscribe.create_subscription(
change_set_name=>'test_cdc',
description=>'change data for sales',
subscription_name=>'sales_sub');
end;
--5.4.訂閱具體的source table和column
begin
dbms_cdc_subscribe.subscribe(
subscription_name=>'sales_sub',
source_schema=>'system',
source_table=>'SALES',
column_list=>'id,productid,price,quantity',
subscriber_view=>'TCDC_VIEW_SALES');
--subscriber_view=>'sales_view');
end;
--5.5.啟用訂閱 不管訂閱包含一個source table還是多個，只需要執行一次啟用即可。
begin
dbms_cdc_subscribe.activate_subscription(
subscription_name=>'SALES_SUB');
end;
--5.6.擴充訂閱視窗 在源表資料變化後，變化的資料在訂閱端需要執行extend_window後才能看見
begin
dbms_cdc_subscribe.extend_window(
subscription_name=>'SALES_SUB');
en
