Splitting LCR's Using Streams

From: Stuart Blackburn <sblackbu_at_cbnco.com>
Date: Fri, 04 Jul 2008 09:04:54 -0400
Message-ID: <486E1FF6.2060605@cbnco.com>

Hi,

I'm looking for some advice on how to split lcr's into multiple tables.

Here is a simplified example of what we need to do:

Database D1 has tables T1,T2,T3
Database D2 has tables T2,T3,T4,T5

According to our business rules nothing can be replicated to database D2
until it reaches a certain status so tables T1,T2,T3 in database D1
will already have been populated when it is time to replicate.

When table T1 reaches the correct status in database D1 the record from
T1 needs to be
replicated and split into tables T4 and T5 in database D2. During this same
transaction the data in tables T2 and T3 in database D1 needs to be
replicated
to tables T2 and T3 in database D2 without any modifications. Tables T2
and T3
contain BLOB info.

I've been experimenting with this using DBMS_STREAMS_MESSAGING.ENQUEUE
and DBMS_AQ.ENQUEUE procedures, but these procedures create user
enqueued messages.  An apply process can only apply changes captured
from a capture process or user enqueued messages, but not both so if I
use any of those methods I need to create multiple processes and
multiple queues which is not desirable.  I can't seem to find any
documentation on this other than manually constructing lcr's and
enqueuing them as user messages.  

Does anyone know if there are any Oracle functions on how to easily capture changes to one table and have it apply the changes on multiple tables?  Any advice/documentation would be helpful.  Thanks.

Stuart

-- http://www.freelists.org/webpage/oracle-l Received on Fri Jul 04 2008 - 08:04:54 CDT