Re: Comparing tables in two DBs

Lou Avrami <lavrami_at_attmail.com> wrote:

>Hello all,
 

> We have an application that runs on UNIX SVR4, Oracle 7.1.6.
 

> Another site is experiencing problems with the application,
>and it appears to be related to the application reference tables
>within the database.
 

> The other site has sent a full export of their database, and
>I have imported it into an instance on one of our local machines. I
>have been charge with the task of comparing the contents of these
>application reference tables (100+) in our database and the import of
>the other site.
 

> Does anyone have any suggestions as to the best way to
>accomplish this? Perhaps the easiest (and ugliest) way to do this
>would be to dump the sorted contents of each imported table into an
>ASCII file, and then run the UNIX commands 'diff' and 'comp' against
>the ASCII counterpart from our local database. I would like to avoid
>doing this.

This may be a bit to simple for your app, but you could create a database link to the 2nd instance, then

SELECT *
FROM table
MINUS
SELECT *
FROM table_at_link

to see the differences. You'll need to

SELECT *
FROM table_at_link
MINUS
SELECT *
FROM table

to get a complete picture.

Hope this helps

Mike Madland
mikemad_at_asymetrix.com

> My initial thoughts are to establish a database link between
>our local (good) DB and the instance with the remote (bad) DB. I then
>want to construct queries that will search for records within each
>table of the good DB, and see if they exist in the table of the bad
>DB. I think that PL/SQL would be better for this than other tools,
>but I'm not sure ... I've only been thinking about how to go about
>this a couple of minutes.
 

> Any thoughts, suggestions, code, examples, etc. would be
>greatly appreciated.
 

>Thanks,
 

>Lou Avrami
>AT&T
Received on Fri Aug 16 1996 - 00:00:00 CEST