Change Data Capture 的简单测试。

最新推荐文章于 2024-02-04 13:49:16 发布

henrybai

最新推荐文章于 2024-02-04 13:49:16 发布

阅读量907

点赞数

文章标签：测试 sql table schema database session

本文链接：https://blog.csdn.net/henrybai/article/details/3934743

版权

-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Wed Feb 25 09:52:15 2009

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL> create user cdcpub identified by cdcpub;

User created.

SQL> grant execute_catalog_role to cdcpub;

Grant succeeded.

SQL> grant select_catalog_role to cdcpub;

Grant succeeded.

SQL> grant create table to cdcpub;

Grant succeeded.

SQL> grant create session to cdcpub;

Grant succeeded.

SQL> grant execute on dbms_cdc_publish to cdcpub;

Grant succeeded.

SQL> show parameter java_pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
java_pool_size big integer 0
SQL> alter system set java_pool_size=48M;

System altered.

SQL> desc scott.emp
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPNO                                     NOT NULL NUMBER(4)
ENAME                                              VARCHAR2(10)
JOB                                                VARCHAR2(9)
MGR                                                NUMBER(4)
HIREDATE                                           DATE
SAL                                                NUMBER(7,2)
COMM                                               NUMBER(7,2)
DEPTNO                                             NUMBER(2)

SQL> grant all on scott.emp to cdcpub;

Grant succeeded.

SQL> begin
2   dbms_cdc_publish.create_change_set(
3   change_set_name =>'emp_cs',
4   description =>'change set ',
5   change_source_name =>'SYNC_SOURCE');
6   end;
7   /

PL/SQL procedure successfully completed.

SQL> alter user cdcpub quota unlimited on users;

User altered.

SQL> begin
2   dbms_cdc_publish.create_change_table(
3   owner =>'cdcpub',
4   change_table_name=>'emp_ct',
5   change_set_name=>'emp_cs',
6   source_schema=>'scott',
7   source_table=>'emp',
8   column_type_list=>'EMPNO NUMBER(4) , ENAME   VARCHAR2(10), JOB   VARCHAR2(9) , MGR   NUMBER(4), HIREDATE DATE , SAL NUMBER(7,2) , COMM NUMBER(7,2) , DEPTNO   NUMBER(2)',
9   capture_values=>'both',
10   rs_id=>'y',
11   row_id=>'n',
12   user_id=>'n',
13   timestamp=>'n',
14   object_id=>'n',
15   source_colmap=>'y',
16   target_colmap=>'y',
17   options_string=>'tablespace users');
18   end;
19   /

PL/SQL procedure successfully completed.

SQL> desc cdcpub.emp_ct
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OPERATION$                                         CHAR(2)
CSCN$                                              NUMBER
COMMIT_TIMESTAMP$                                  DATE
RSID$                                              NUMBER
SOURCE_COLMAP$                                     RAW(128)
TARGET_COLMAP$                                     RAW(128)
EMPNO                                              NUMBER(4)
ENAME                                              VARCHAR2(10)
JOB                                                VARCHAR2(9)
MGR                                                NUMBER(4)
HIREDATE                                           DATE
SAL                                                NUMBER(7,2)
COMM                                               NUMBER(7,2)
DEPTNO                                             NUMBER(2)

SQL>
SQL> create user cdcsub identified by cdcsub;

User created.

SQL> grant create session to cdcsub;

Grant succeeded.

SQL> grant create table to cdcsub;

Grant succeeded.

SQL> grant select on cdcpub.emp_ct to cdcsub;

Grant succeeded.

SQL> conn cdcsub/cdcsub;
Connected.
SQL> select * from all_source_tables;

SOURCE_SCHEMA_NAME SOURCE_TABLE_NAME
------------------------------ ------------------------------
SCOTT EMP

SQL> select distinct change_set_name,column_name,pub_id
2 from all_published_columns
3 where source_schema_name='SCOTT' and source_table_name='EMP';

CHANGE_SET_NAME                COLUMN_NAME                        PUB_ID
------------------------------ ------------------------------ ----------
EMP_CS                         COMM                                56285
EMP_CS                         DEPTNO                              56285
EMP_CS                         EMPNO                               56285
EMP_CS                         ENAME                               56285
EMP_CS                         HIREDATE                            56285
EMP_CS                         JOB                                 56285
EMP_CS                         MGR                                 56285
EMP_CS                         SAL                                 56285

8 rows selected.

SQL> begin
2   dbms_cdc_subscribe.create_subscription(
3   change_set_name=>'emp_cs',
4   description=>'change data for emp',
5   subscription_name=>'emp_sub');
6   end;
7   /

PL/SQL procedure successfully completed.

SQL> begin
2   dbms_cdc_subscribe.subscribe(
3   subscription_name=>'emp_sub',
4   source_schema=>'SCOTT',
5   source_table=>'EMP',
6   column_list=>'EMPNO , ENAME , JOB   , MGR , HIREDATE , SAL , COMM , DEPTNO ',
7   subscriber_view=>'emp_view');
8   end;
9   /

SQL>desc emp_view

Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
OPERATION$                                         CHAR(2)
CSCN$                                              NUMBER
COMMIT_TIMESTAMP$                                  DATE
RSID$                                              NUMBER
SOURCE_COLMAP$                                     RAW(128)
TARGET_COLMAP$                                     RAW(128)
COMM                                               NUMBER(7,2)
DEPTNO                                             NUMBER(2)
EMPNO                                              NUMBER(4)
ENAME                                              VARCHAR2(10)
HIREDATE                                           DATE
JOB                                                VARCHAR2(9)
MGR                                                NUMBER(4)
SAL                                                NUMBER(7,2)

SQL>   begin
2   dbms_cdc_subscribe.activate_subscription(
3   subscription_name=>'EMP_SUB');
4   end;
5   /

PL/SQL procedure successfully completed.

SQL> begin
2    dbms_cdc_subscribe.extend_window(
3    subscription_name=>'EMP_SUB');
4    end;
5    /

PL/SQL procedure successfully completed.
SQL> conn scott/oracle
Connected.
SQL> insert into emp (empno,ename,deptno) values(6666,'Henry',10);

1 row created.

SQL> commit;

Commit complete.

SQL> conn cdcsub/cdcsub
Connected.
SQL> SELECT * FROM EMP_VIEW;

I 8.3731E+12 25-FEB-09          1
FE010000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000
FE7F0000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000
                   30       8888 HENRHY               DBA



SQL> begin
2   dbms_cdc_subscribe.extend_window(
3   subscription_name=>'EMP_SUB');
4   end;
5   /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM EMP_VIEW;

I 8.3731E+12 25-FEB-09          1
FE010000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000
FE7F0000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000
                   30       8888 HENRHY               DBA


I 8.3731E+12 25-FEB-09          2

FE010000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000
FE7F0000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000
                   10       6666 Henry