Re: Just need a small hint re: mirroring tables

Paul Murphy was kind enough to write:
> Daniel Morgan wrote:
> > Paul Murphy wrote:
> >
> >
> >><snipped>
> >>
> >>I'm trying to get two pieces of software to share the same data, but
> >>they both have hardcoded into the executables the different
> >>schema.tablename. Because I can't modify the software, I have to create
> >>two duplicate tables of the same name in two separate schemas, but I
> >>need both executables using the same data, so I need them to be
> >>constantly synchronized. Maybe this is not a common problem and that's
> >>why I'm having so much trouble finding anything in books. Any ideas
> >>given the situation? Thanks! -Paul
> >
> >
> > No you don't. Look up synonyms at http://tahiti.oracle.com
> >
> > If you disagree post the names of the software manufacturers, the applications, and the names of the tables.
> >
> > This reads as somehwere between highly unlikely and not very believable.
> > --
> > Daniel Morgan
> > http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
> > damorgan_at_x.washington.edu
> > (replace 'x' with a 'u' to reply)
> >
> >
>
> The apps are Maximo and Lawson, the tables are: uzbprem, uzbmetr and
> uzbsvco. The Maximo schema and the Lawson schema are in the same
> database (Oracle 8i / Win2K). I understand what you said about granting
> all to user2 and creating a private synonym inside user2's schema and
> that certainly sounds like the correct thing to do to me. When I
> suggested this approach, I was told:
>
> 1. They don't want to give any permissions whatsoever across schemas
> (even though the data is going to be the exact same data and permissions
> would be granted only on those 3 specified tables).
>
> 2. They are worried about Maximo putting locks on the table and Lawson
> not being able to handle the situation (both apps are supposedly
> designed to assume 100% availablity to those 3 tables for their own use).
>
> I know this sounds irrational, but their attitude is: "We want mirrored
> tables in separate schemas, period. Can you do it or not?"
>
> I think I can do it with something like this:
>
> CREATE OR REPLACE TRIGGER MyTrigger
>
> AFTER INSERT OR UPDATE OR DELETE ON Schema1.Table1
>
> FOR EACH ROW
>
> BEGIN
>
> IF INSERTING THEN
>
> CODE
>
> ELSIF UPDATING THEN
>
> CODE
>
> ELSIF DELETING THEN
>
> CODE
>
> END IF;
>
> END;
>
> /
>
> I'll fill in the code areas myself, I'm not asking for that type of
> help, just to know if there is a more efficient way to mirror tables
> across schemas other than the type of trigger I posted above.
>
> It does seem like a bad idea from a design standpoint to mirror the
> tables, but I have no choice except to have mirrored tables unless there
> is a way to eliminate their 2 requirements (permissions and locks).
>
> I really do appreciate getting advice and when I've learned enough, I'll
> be in a position to help the future newbies. Thanks again.
>
> -Paul
>
>
>
>

Yes, triggers like this are an acceptable way. At one company where I worked, we used this technique to sync several "data marts" on different servers.

Sigh. If you had 9i, I could recommend Oracle Streams. Quoting from the 9i New Features manual:

"Oracle Streams enables the propagation of data, transactions and events in a data stream, either within a database or from one database to another. The stream routes published information to subscribed destinations. This provides the functionality and flexibility to capture and to manage events and then to share those events with other databases and applications..."

Since it apparently uses Advanced Queueing, perhaps you can implement something similar in 8i. Or it might be a good time to push for an upgrade to 9i. :)

-- 
[:%s/Karsten Farrell/Oracle DBA/g]