ORACLE 捕捉变化数据实例 CDC （oracle change data capture）

1.修改java_pool_size
  ALTER SYSTEM SET JAVA_POOL_SIZE = 5000000;
2. 创建表空间
  CREATE TABLESPACE TS_CDCPUB DATAFILE 'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/CDCPUBDATA.DBF' SIZE 100M;
3. 创建查询用户
   CREATE USER cdcpub IDENTIFIED BY cdcpub DEFAULT TABLESPACE ts_cdcpub
   QUOTA UNLIMITED ON SYSTEM
   QUOTA UNLIMITED ON SYSAUX;
   GRANT CREATE SESSION TO cdcpub;
   GRANT CREATE TABLE TO cdcpub;
   GRANT CREATE TABLESPACE TO cdcpub;
   GRANT UNLIMITED TABLESPACE TO cdcpub;
   GRANT SELECT_CATALOG_ROLE TO cdcpub;
   GRANT EXECUTE_CATALOG_ROLE TO cdcpub;
  
   GRANT ALL ON SCOTT.EMP TO cdcpub;

   GRANT EXECUTE ON DBMS_CDC_PUBLISH TO cdcpub;

4. 创建变更集
    begin
      dbms_cdc_publish.create_change_set(
         change_set_name=>'DEMO_DAILY',
         description=> 'Change Set for emp_demo table',
         change_source_name=>'SYNC_SOURCE');
   end;
   /

5. 创建变更表
   begin
      dbms_cdc_publish.create_change_table(
           owner            =>'cdcpub',
           change_table_name=>'emp_demo_changes',
           change_set_name  => 'DEMO_DAILY',
           source_schema    =>'SCOTT',
           source_table     =>'EMP',
           column_type_list =>'EMPNO NUMBER,  ENAME VARCHAR2(10),
                               SAL NUMBER(7,2)',
           capture_values=> 'BOTH',
           RS_ID=> 'Y',
           ROW_ID=>'Y',
           USER_ID=>'Y',
           TIMESTAMP=>'N',
           OBJECT_ID=>'N',
           SOURCE_COLMAP=>'Y',
           TARGET_COLMAP=>'Y',
           OPTIONS_STRING => ' TABLESPACE TS_CDCPUB pctfree 5 pctused 95' );
   end;
   /
   grant select on cdcpub.emp_demo_changes to cdcpub;
6. 实验

    登陆：conn scott/tiger@orcl
   变更表：insert + update
   insert into emp(empno,ename,sal) values ('9037','yyq',5000);
   update emp set sal = sal * 1.2 where empno = '7369';
   登陆： conn cdcpub/cdcpub@orcl
   查询变化的数据：从变更表中
   select * from emp_demo_changes；
   结果：

OPERATION$	CSCN$	COMMIT_TIMESTAMP$	RSID$	ROW_ID$	USERNAME$	SOURCE_COLMAP$	TARGET_COLMAP$	EMPNO	ENAME	SAL
I	281474976710655	4000-1-1	16	AAAMfPAAEAAAAAdAAA	SYS	FE0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000	FE0F00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000	9037	yyq	5000.00
UU	281474976710655	4000-1-1	15	AAAMfPAAEAAAAAgAAA	SCOTT	400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000	FE0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000	7369	SMITH	800.00
UN	281474976710655	4000-1-1	15	AAAMfPAAEAAAAAgAAA	SCOTT	400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000	FE0900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000	7369	SMITH	960.00