Skip to main content

Set up CDC in Oracle

To do so, create a tablespace for the source user and the publisher respectively, then create a source user and give it all the rights necessary to make modifications, and create a publisher and give it all the rights necessary to capture and publish modifications.

In the example below, the $ORACLE_PATH varies depending on where Oracle is installed. The source user is called source, and the publisher is called publisher:

create tablespace SOURCE datafile '$ORACLE_PATH/oradata/Oracle/SOURCE.dbf' size 50M;
create user source
identified by source
default tablespace SOURCE 
quota unlimited on SOURCE;
grant connect, create table to source;
grant unlimited tablespace to source;grant select_catalog_role to source;
grant execute_catalog_role to source;
grant create sequence to source;
grant create session to source;
grant dba to source;
grant execute on SYS.DBMS_CDC_PUBLISH to source;
create tablespace PUBLISHER datafile '$ORACLE_PATH/oradata/Oracle/PUBLISHER.dbf' size 50M;
create user publisher
identified by publisher
default tablespace PUBLISHER
quota unlimited on PUBLISHER;
grant connect, create table to publisher;
grant unlimited tablespace to publisher;
grant select_catalog_role to publisher;
grant execute_catalog_role to publisher;
grant create sequence to publisher;
grant create session to publisher;
grant dba to publisher;
grant execute on SYS.DBMS_CDC_PUBLISH to publisher;
execute DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE=>'publisher');

The select_catalog_role procedure allows the publisher to consult all Oracle dictionaries.

The execute_catalog_role procedure allows the publisher to execute the dictionary procedures.

The SYS.DBMS_CDC_PUBLISH procedure allows the publisher to configure the CDC system that will capture and publish change data in one or more source tables.

The procedure: DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(GRANTEE=>'publisher')gives the user the administration privileges necessary to carry out data replication (stream). The GRANT_ADMIN_PRIVILEGE procedure allows the user to carry out data capture and propagation operations.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!