Re: Q: Comparing two tables in Oracle

In article <3npfb4$mki_at_oznet03.ozemail.com.au>, npbs_at_ozemail.com.au (Newcastle Permanent Building Society) writes:
::From: npbs_at_ozemail.com.au (Newcastle Permanent Building Society)
::Newsgroups: comp.databases.oracle
::Subject: Q: Comparing two tables in Oracle
::
::Currently our site is about to undertake a major migration to Oracle from
::a proprietary platform. To test whether the conversion has been
::successful we need to compare groups of tables in the same tablespace,
::different tablespaces, and different databases.
::
::e.g. Comparing the data in BORROWER table with data in
:: OLD_BORROWER table
::
::We will be dealing with quite large tables and our current approach was
::that programs were written in PRO*COBOL to compare data on a field level
::and spitting out details when fields did not match. This was a very slow
::option and I was wondering if there was any other way to do it or if
::there was some third party software that existed to do this.
::
::
::Thanks in advance for any help or suggestion,
::
::PETER HAYES DBA, Analyst/Programmer, Chief cook and Bottlewasher
::
::

-- 
 Peter,

 Assuming the two tables to be compared are of exactly the same format
 as far as column definitions and sequence of the columns are concerned
 it may be useful to try something along the lines of 

 select * from borrower
 minus 
 select * from old_borrower;

 and 

 select * from old_borrower
 minus 
 select * from borrower

 The first will select all information from each row in BORROWER which is
 not exactly matched by a corresponding row in OLD_BORROWER and the second
 will do the reverse. Of course you may get the same rows returned from 
 both but this solution does cater for a corresponding row being missing
 from the other table. Performance may be an issue depending upon your
 volume

 Hope this gets you started - feel freee to E-MAIL me for any further 
 help I can give.


  Ian
+--------------------------+-------------------------+-----------------------+
| Ian Bainbridge           | bainbridge.i_at_bgers.co.uk|Phone: (44)191-216-0202|
| British Gas ERS          | Newcastle Upon Tyne, UK |            x2790      |
+--------------------------+-------------------------+-----------------------+