Change data capture implementation in Oracle Data warehouses – Part 3

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

  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.

Latest Articles