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 |