Change data capture implementation in Oracle Data warehouses - Part 3 - Page 2

May 29, 2003

Publishing change data

In order to publish data, the publisher (DBA) has to:

  • Identify the source system (Oracle instance) and the source tables in which the change data has to be captured.

  • Have EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE privileges and privilege to GRANT SELECT on the change tables to the subscribers.

  • Set up the change data capture system to capture and publish changed data using DBMS_LOGMNR_CDC_PUBLISH procedures.

Subscribing to change data

Subscribers, typically data warehouse applications that consume change data, have to:

  • Identify the source system (Oracle instance) and the source tables from which the change data has to be captured.

  • Subscribe to the change tables. The subscriber can access only the change tables that have SELECT privileges granted to the subscriber by the publisher.

  • Activate the subscription, create the subscription window and the subscriber views to view and query the change data.

  • Drop and create new subscription windows and subscriber views.

  • The subscriber does not directly access the change tables and gets only unique change data from the source tables or the change tables.

  • To set up change data subscription, the subscriber has to use the SYS owned DBMS_LOGMNR_CDC_SUBSCRIBE procedures.

  • If at any time the publisher makes changes to the change tables, then the subscriber gets appropriate database error exceptions and no message notifications.


In part three of this series, we discussed Oracle's change data capture features, Oracle Streams and the CDC framework. As mentioned the CDC feature was introduced in Oracle9.0 while Oracle Streams is the newest addition to the list of new features.

While Oracle Streams provide a host of features including change data capture from different databases to messaging notification across a broad network of databases (or data warehouses, data marts, ODS etc.), CDC is limited to low volume databases and capturing only DML changes from an Oracle database.