Re: Just need a small hint re: mirroring tables

From: Paul Murphy <pmurphy_at_scsinet.com>
Date: Wed, 02 Jul 2003 16:40:30 GMT
Message-ID: <2WDMa.31337$iZ3.30149@twister.nyroc.rr.com>

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 Received on Wed Jul 02 2003 - 11:40:30 CDT