Retrieving information updated/added/deleted

Hello,

We are working on Oracle 8i ( 8.1.7 ).

We would like to retrieve quickly all the information ( structure and data ) modified since a short time ... This is for synchronisation between 2 databases ...

What we have is :

• DB1 : Production Database that is often modified by users.
• DB2 : Mirror Database that contains the META DATA of the DB1 and the data also.

The DB1 has many records ( more than 30 millions records in the biggest table - Insurance domain - with maybe some thousands modifications a day ) and DB2 has to be refreshed every 2 hours. How to list all the modified information from DB1 when DB2 is refreshing.

One of the solution we tried was putting a trigger on all the tables "After Insert", "After Update" that insert on a table RECENT_MODIF but this is really time consuming.

I know all the modification are stored in the redo logs, but reading them is pretty complex...

Would you know any other way to do such ?

As the DB1 is huge, if I put a flag on that for retrieving quickly all the modified information, it will be so long to query on that, and, if we put an index on that column, it will be more time consumming for the production ...

Thanks in advance,

Regards.

Stef Received on Fri Dec 06 2002 - 04:55:22 CST