Re: Extracting only changed data

You might consider setting up a 10g database and using Change Data Capture (or a full blown Streams implementation) on the 9i database to get data into the 10g database.

If you use one of the asynchronous methods, the 9i instance can read it's redo log file and forward the changes to the 10g database through the streams mechanisms in near real time.

Mark Rittman has a recent blog about this (in the context of BI), and references a 'cookbook' on OTN that demonstrates setting up an option known as

    asynchronous distributed hotlog change data capture.

The capture on the source database is 'invisible' to the application, although the overhead could be a few percent. In the cookbook, the work you would have to do at the target end is not specified - and the way that the standard CDC mechanism operates at the target is not the most efficient strategy for dealing with your problem; but it's a little easier than doing the whole think manually through streams.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html


<joel-hammond-smith_at_hotmail.com> wrote in message 
news:1145936026.246473.240940_at_v46g2000cwv.googlegroups.com...


>

> We have a large (100's gigs) OLTP (v9) database which is propreitary to

> a vendor.  We would like to extract data near real time for reporting

> when the system becomes unavailable.

>

> Ideally we would extract on the inserted or updated data versus pulling

> it out all out.

>

> We cannot add insert/update triggers to tables but is there an

> alternative which fulfills the same requirement.

>

> I would imagine it to be a 'virtual trigger' which doesnt screw up the

> vendor schema - but tells us when data is changing in a table.

>

> Wishful thinking perhaps....?

>

> Joel

>