Re: A replication question/mlml

The way you describe it, you have a small table in one database that captures the data, and has to forward the data on a fairly
regular basis to a database that accumulates the entire history of captured data.

What happens to the small table after the data has been forwarded ? Is the data
cleaned out ready for the next batch to
accumulate, or does it stay there being
updated?

When the data is sent to the large table do you guarantee that all the rows should be inserted into the large table, or might some of them have to be updates ?

If my initial assumptions are correct, then you could do something like this:

    Make the small table a partitioned table     with a single partition.

    On demand (or through dbms_job)

        exchange the partition with an empty table.
        this isolates the data from new incoming
        data and makes it easy to make sure that
        you go wrong in a two-step insert/delete.
        (An alternative is to play around with
        synonyms or views to make the same
        break).

        use the insert command across a database
        link to copy the data from the standalone
        table you've just pulled from the partitioned
        table.  (You could use a MERGE if the data
        is partly update, partly insert)

        truncate the standalone table (or rename
        it and keep a few historical copies just
        in case).

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

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

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"Michel Lee" <ae299_at_FreeNet.Carleton.CA> wrote in message
news:c9m69k$c2t$1_at_freenet9.carleton.ca...


>

> A replication question/mlml

>

> Hi,

> I have 2 sites far apart(Asia & North America),

> one with a large table , the other with a small table, same structure.

>

> Inserts are done in the small table.

> I would like to copy this data to the large table routinely.

>

> (North America) large table <-----<------ small table (Asia)

>

> How would I do it?

> I thought of a few ways but they dont really fit in.

> 1) MultiMaster Replication (NO)

> 2) Materialized view (NO)

> 3) Updateable Materialized view (NO)

>

> Maybe this is not a replication question?

> This seems like a simple question, but how would I do this?

>

> thanks in advance

> Mike

>

> --

> /-------------------------------------------------------------------/

> /             http://miccc.com                                      /

> /-------------ae299_at_ncf.ca------------------------------------------/