Re: A replication question/mlml

Late into this one, sorry.

How about a simple read only snapshot (refresh fast) of the small table in the database with the large table?
Then put an after insert row level trigger on the snapshot, which simply inserts :new.* (so to speak) into the large table. That way your local snapshot of the small table will stay small, while the large table will just accumulate all the inserts.

Hope this helps,
Paul

"Michel Lee" <ae299_at_FreeNet.Carleton.CA> wrote in message news:c9q370$av0$1_at_freenet9.carleton.ca...
> Thanks for the reply.
> I have some comments,...
>
> "Jonathan Lewis" (jonathan_at_jlcomp.demon.co.uk) writes:
> > 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?
>
>
> it rows stays there for a few months before it gets deleted.
> only inserts should be copied, update will not be performed.
>
> >
> > 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 ?
>
> all will be inserts.
>
> >
> >
> > 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).
> >
> >
>
> i was hoping of an efficient method.
> i guess you recommend this way...
> some people say to use updateable materialized view,
> i dont see how that way would be done.
> updateable materialized view is 2-way, i only need 1 way.
>
> cheers,
> Mike
>
>
> > --
> > 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------------------------------------------/
> >
> >
>
>
> --
> /-------------------------------------------------------------------/
> / http://miccc.com /
> /-------------ae299_at_ncf.ca------------------------------------------/
Received on Mon Jun 07 2004 - 12:30:26 CDT