Compare data in tables of different structures

Hi, dear all,

Currently we have two Oralce 9i databases in the production environment. We synchronize them *logically* every day. *logically* means these 2 databases are not exactly identical, the schemas inside are different, the table structures inside are different too, but we synchronize the data in the 2 databases in daily basis, bi-directional.

Just a simple example: In DB 1, we have table 1, and in DB 2, we have table 2 and 3. Table 2 and 3 together contain the same data as table 1 does. Another scenario is, two tables contain the same data, but having different structures. I hope you have gotten the meaning. ( It is much more complex than this simple example in our reality, and impossible to alter the table structures too, due to the existing business rules).

The problem we are facing is, the databases don't synchronize well with each other, some records gone missing, some columns of records become different, etc.(Currently some other people manually examine them and update ). We use xml files to synchronize.

We know the corresponding tables, and now we need to find and analyze the record-level and column level differences among them. Yes, find the differences first only, not solve them at this stage.

I have thought of 4 ways to tackle it:

1. Create identical Views out of the corresponding tables. and compare the views first. then trace the view data to their tables. Is this a good option in terms of viability and complexity?
2. Write PL/SQL scripts. I haven't thought of how to write them, but I think this programming can solve it, but can be very difficult and complex.
3. Buy 3rd party tools, such as DBDiff or dbBalance. This will incur extra cost and at this moment, my director is not approval of this.
4. Any Oracle9i built-in tools to help?

Do you have any comments and ideas of whether these ideas are possible? or any other better solution? Thanks in advance.

Regards,
Warren

PS: I am not really an oracle and db expert, but assigned this task. and we will migrate this system to a new one, but at this moment we have to solve this for the production people. Received on Tue Jan 03 2006 - 19:57:17 CST