Re: Compare data in tables of different structures

warren.liyongbo_at_gmail.com wrote:
> 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.
>

You cannot. Not without clearly defined rules about who is in charge. What if data server 1, table A differs from server 2 table A, *AND* vice versa? Which Table A do you believe to hold the correct data?!?
> 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).

Of course you can change the rules! Consider what is more important: a rewrite, or corrupt data.
And tell management to hurry up with the decision what system to use; one of the two companies may lose face, but hey, that's to happen with mergers.

> I have thought of 4 ways to tackle it:
I see only 2; 1 and 2 are two different tasks within the same approach, 4 is just a question.
>
> 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?

Materialized views, Advanced Replication or Streams. Oh - and 2) is on target: it's complex, and not designed properly.

Rest in line...

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...