Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted May 30, 2003

Change data capture implementation in Oracle Data warehouses - Part 3

By DatabaseJournal.com Staff

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.

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM