sybrandb_at_yahoo.com wrote:
> Paul Murphy <pmurphy_at_scsinet.com> wrote in message news:<EYqMa.28406$iZ3.16890_at_twister.nyroc.rr.com>...
>
>>Oracle 8i / Win2K
>>
>>This seems absurdly simple, but there isn't any coverage of it in all my
>> Oracle books or in several Google searches.
>>
>>I have 2 duplicate tables in different schemas of the same database. All
>>I need to do is continuously synchronize table2 to always update itself
>>to be the same as table1 every time table1 changes.
>>
>>My first thought was to write a trigger that covers insert, update and
>>delete events on table1. I started to write it (using :old and :new
>>values for the update part). I think this will work fine, but I thought
>>I might be hand writing a built-in feature of Oracle.
>>
>>Then I got an idea that it might be better to do a truncate table2,
>>insert into table2 select * from table1 for each event. This doesn't
>>sound like a good idea performance-wise, although the tables are on the
>>small side (just ordinary varchars and numbers, about 30 cols, a few
>>hundred rows).
>>
>>Then I thought, I wonder if materialized views would be more efficient,
>>although all the chapters about them concern moving subsets or
>>calculated data to a remote database.
>>
>>Finally, I thought one of you might be kind enough to just give me a
>>nudge in the right direction:
>>
>>1. Stick to the triggers.
>>2. Go for the truncate method.
>>3. Use materialized views.
>>4. Go with the super-secret Oracle Mirrored Tables option (that I
>>suspect is there, but can't find).
>>
>>
>>Thanks!
>>-Paul
>
>
>
> Forget about 4. It doesn't exist. The obvious reason of course is you
> will have a database with a completely out of order datamodel.
> I would go for
> 5 Drop either one of the tables and set up proper synonyms and grants
> Without that you will continue symptom figthing and end up in a mess.
> IMO, any other solution is just stupid.
>
> Sybrand Bakker
> Senior Oracle DBA
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
Received on Wed Jul 02 2003 - 07:58:02 CDT
