Use the 10 Gb internal ETL infrastructure of the Oracle database

Use Oracle Database 10 GInternal ETL infrastructure

Http://www.oracle.com/technology/global/cn/obe/10gr2_db_single/bidw/etl2/etl2_otn.htm



-- Some basic concepts and types of CDC are introduced in Change Data Capture (1. This article mainly demonstrates the basic steps of implementing the synchronization mode CDC through a practical example.
-- Create table
Create table SALES
(
  ID             NUMBER,
  Productid number,
  PRICE       NUMBER,
  QUANTITY NUMBER
)
-- Url: http://www.ningoo.net/html/tag/cdc
-- 2. 1. Create a user as the publisher in source database
Create user cdcpub identified by cdcpub;
-- 2. 2. grant corresponding Permissions

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; -- Error
-- 3.1
Alter system set java_pool_size = 48 M;

-- 4.1
Grant all on system. sales to cdcpub; -- Error
-- 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'); -- Add a new parameter for Oracle11
End;
-- 5.1
Create user sp_sub identified by sp_sub;
Grant create session to Fig;
Grant create table to Fig;
Grant select on cdcpub. sales_ct to Fig;
-- 5. 3. Create a subscription
Begin
Dbms_cdc_subscribe.create_subscription (
Change_set_name => 'test _ cdc ',
Description => 'change data for sale ',
Subscription_name => 'sales _ sub ');
End;
-- 5. 4. subscribe to the specific source table and 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. Activate the subscription. no matter whether the subscription contains one or more source tables, you only need to activate the subscription once.
Begin
Dbms_cdc_subscribe.activate_subscription (
Subscription_name => 'sales _ sub ');
End;
-- 5. 6. Extend the subscription window After the data in the source table changes, the changed data can be seen only after extend_window is executed on the subscription end.
Begin
Dbms_cdc_subscribe.extend_window (
Subscription_name => 'sales _ sub ');
En