实时捕捉oracle数据变化,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