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

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

News Via 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 - Page 2

By DatabaseJournal.com Staff

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.

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