Change data capture implementation in Oracle Data warehouses - Part 3

Thursday May 29th 2003 by Staff

This article continues our series on change data capture implementation in Oracle Data warehouses. Learn about the change data capture model and how to publish and subscribe to change data.

Earlier in this series, we discussed Oracle streams framework and a simple Streams configuration. While Oracle Streams (new feature in Oracle 9.2.0) provides for multitude of functions such as messaging, event management, DML and DDL change capture from disparate databases etc., Change data capture (from Oracle 9.0) can be used only for DML operations on the source tables in an Oracle system

Essentially, in change data capture system, DML operations on the source tables are captured synchronously or continuously and in real time.

The change data capture model

The components in the model can be described as:

  1. The source system is typically a low volume Oracle based OLTP production database. Because the change data capture captures changes to the source table continuously and in real time, significant overheads are incurred during capture time.

  2. The change source (SYNC_SOURCE is the system generated change source), represents the source system and contains change set (collection of change tables.)

  3. The change set (SYNC_SET is the system generated change set) is a collection of change tables.

  4. The change table is table that contains all the source table data changes and also system metadata necessary to maintain the change table such as "username$", "timestamp$" etc.

  5. The publisher, usually a DBA who is responsible for setting up the change data capture data system and maintaining it. The publisher identifies the source tables from which the changed data has to be captured and published to the change tables.( as in the case of extraction and generating a flat file). The publisher also controls access to this published data by using GRANT and REVOKE privileges on the change tables.

  6. The subscriber is typically a datawarehouse application that consumes the changed data. The subscriber subscribes to one or more sets of columns in the source tables (Subscriber views) and can receive a set of change data in specific time window (sets of rows or subscriber window).The subscriber should SELECT priveleges on the changes tables in order to subscribe to the published data.

Unlike Oracle Streams that can be configured in a number of ways to capture different kind of changes and transformation, the change data capture is a very straightforward process and captures any DML operations on the columns of the source tables as indicated in the definition of change tables.

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.

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved