That sounds a lot like materialized views. On Thu, 2003-08-28 at 08:14, Igor Neyman wrote: > It could be a combination of trigger/pooling. > Trigger writes changes locally into some kind "queue" table. > The second instance is pooling this "queue" table (using db link) at > it's own rate without affecting transactions against original table. > > Also, in this case when network is down, original instance is not > affected, and when network restored the second instance picks up where > it stopped before network was down. > > I have this mechanism implemented here, and it works pretty smoothly. > > Igor Neyman, OCP DBA > [EMAIL PROTECTED] > > > > -----Original Message----- > Stephane Faroult > Sent: Thursday, August 28, 2003 6:00 AM > To: Multiple recipients of list ORACLE-L > > > > > >Hi listers, > >=20 > >Assume that there are two instances in Oracle. Both > >instances are on = > >different machines and different Oracle versions. > >There is a table on = > >first instance. Any update on this table should > >invoke stored procedures = > >on the second instance. This should be real time > >based. Options we = > >looked at are > >=20 > >1. Trigger on the table invoking the procedures of > >the other instance > >2. Using dbms_alert > >3. Some kind of polling mechanism > >=20 > >Triggers we would like to avoid. Options we are > >left with are dbms_alert = > >and polling mechanism.=20 > >=20 > >Is it possible to use dbms_alert in this case? If > >yes how? > >=20 > >Can you think of some kind of polling mechanism > >which will satisfy the = > >need of real time communication? Updates on the > >table is done at a very = > >fast rate, hence processing should also be at a > >fast rate. > >=20 > >Any help in this regard is very much appreciated. > >=20 > >Thanks and Regards, > >=20 > >Ranganath > >=20 > > I agree with your reluctance to use triggers; the problem is that > whenever the second instance is down, then you couldn't do anything on > the first. Basically, what you want to implement are near real-time > although not quite synchronous snapshots. > I have never used DBMS_ALERT in this way, but I think that it would be > possible to have a database link on the second instance referencing the > first one and invoking DBMS_ALERT through it. Beware with DBMS_ALERT > though, my memories are not very fresh but there are some problems with > COMMITs (which you can workaround with autonomous transactions, but then > the alertee can be woken up by a rolled back transaction, a case which > has to be handled by your code); DBMS_PIPE is another solution, which > also has its flaws. > Avanced queuing seems to me to be a fine mess, but perhaps it's worth a > look too. > > Regards, > > Stephane Faroult > Oriole > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > --
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
