我家小花儿

这个屌丝很懒，什么也没留下！

热门标签

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

作者：我家小花儿 | 2024-07-14 22:10:04

踩

oracle cdc demo

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 http://blog.csdn.net/dnnyyq/article/details/mailto: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 http://blog.csdn.net/dnnyyq/article/details/mailto:cdcpub/cdcpub@orcl

查询变化的数据：从变更表中

select * from emp_demo_changes；

结果：

OPERATION$

CSCN$

COMMIT_TIMESTAMP$

RSID$

ROW_ID$

USERNAME$

SOURCE_COLMAP$

TARGET_COLMAP$

EMPNO

ENAME

SAL

281474976710655

4000-1-1

AAAMfPAAEAAAAAdAAA

SYS

FE0100000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

FE0F00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

9037

yyq

5000.00

281474976710655

4000-1-1

AAAMfPAAEAAAAAgAAA

SCOTT

400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

7369

SMITH

800.00

281474976710655

4000-1-1

AAAMfPAAEAAAAAgAAA

SCOTT

FE0900000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

7369

SMITH

960.00

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/我家小花儿/article/detail/826485