Re: Oracle Streams / Change Data Capture Question

andrewah@xxxxxxxxxxx wrote:
Hi,

I wondered if you could advise me on what Oracle feature to look into
to help me decide upon a design issue?

we have a corporate database - our main system - which is effectively
the single repository for data in the organisation.

A need for a publically available extranet has arisen, whose
underlying database schema will contain a replica of some of the main
system's data.

About 10 tables have to be populated in the extranet's database. Their
data will be derived from data stored in the main system. Some, but
not all of these tables have a direct equivalent in the source system
- ie the data that some tables will store is derived from a number of
tables in our source system.

If records in two of the tables in the main system are flagged to be
pushed to the website's database by business users [which could happen
at any time], it is possible to derive what the remainder of the data
destined for the website's database should be.

Clearly, it would be very simple to implement a push of data from the
source to the destination database just by putting triggers on the two
tables previously mentioned to capture changes to their records, using
some queries to bring the data together, and then send these resulting
datasets off to the destination system.

The question I have is whether or not a feature like 'Oracle Streams'
or 'Change Data Capture' would be useful in this situation? Is it
possible to configure either of these features to recognise that when
a change is made to a particular record, that Oracle should not only
propogate this change to a destination system, but also propogate a
related dataset as well? A feature like Streams would certainly
simplify catering for situations where reference data in the main
system changed after it was pushed to the destination.

Any information would be appreciated,

Cheers,

Andrew.


If you don't need real-time data, consider doing ETL from source to target. This can simplify the maintenance tasks involved on a Streams and CDC scenario.

For loading data from one database to another you can take a look at
the free warehouse builder also from oracle.

cheers,
pedro


.

Relevant Pages

  • Re: Oracle Streams / Change Data Capture Question
    ... we have a corporate database - our main system - which is effectively ... datasets off to the destination system. ... The question I have is whether or not a feature like 'Oracle Streams' ...
    (comp.databases.oracle.server)
  • Oracle Streams / Change Data Capture Question
    ... we have a corporate database - our main system - which is effectively ... datasets off to the destination system. ... The question I have is whether or not a feature like 'Oracle Streams' ...
    (comp.databases.oracle.server)
  • Sticky destination database name
    ... I build a fancy DTS package to migrate data from a source database S ... actually build the package to use destination X. ... Is it a feature? ...
    (microsoft.public.sqlserver.dts)
  • Re: What so special about PostgreSQL and other RDBMS?
    ... That's exactly the link the licence agreement for the database points to when it ... comes to what wecan expect for paying support. ... > "Oracle may provide additional releases or versions of its programs ... If the requirements are volatile I'd do a long term contract detailing what ...
    (comp.lang.php)
  • A cool DBA job wanted
    ... 7, Oracle 6, Sybase, SQL Server ... Proposed proactive database monitoring through ... strategies to administer remote Oracle databases ... Trained installation and support personnel in basic ...
    (comp.databases.oracle.server)