comparing tables

My employer has asked me to compare two databases which are not connected (1 in Oracle 7, and 1 in oracle 8). but with more or less the same set of tables and records (let's say: custumors and addresses). Can anyone give me a hint how to compare those tables. I need to know which records in one table are missing in the other one? I need to know which records have the same unique identification but different other attributes, etc.First, you need to establish a database link from one database to the other, so that you can connect to one database and select data from the other, e.g.

DB1> SELECT * FROM scott.emp@.DB2;

Your DBA should be able to help with this.

Then you can compare records. To see which records in DB1 are missing in DB2, run this in DB1:

SELECT keycol1, keycol2, ... FROM table1
MINUS
SELECT keycol1, keycol2, ... FROM table1@.DB2;

To find records where keys match but other attributes differ:

SELECT t1.keycol1, t1.keycol2, ...
FROM table t1, table@.DB2 t2
WHERE t1.keycol1 = t2.keycol1
AND t1.keycol2 = t2.keycol2
...
AND ( t1.attribute1 != t2.attribute1
OR t1..attribute2 != t2.attribute2
OR ...
);

BEWARE OF NULLS!!!

If attribute1 can be NULL, then you must allow for that. The correct way is:

NOT ( ( t1.attribute1 IS NULL AND t2.attribute1 IS NULL)
OR t1.attribute1 = t2.attribute1
)

A simpler way is:

NVL(t1.attribute1,'?') = NVL(t2.attribute1,'?')

... but you must be sure to pick a suitable value (e.g. '?') that is (a) valid for the datatype of attribute1 and (b) not a value that you will actually find in attribute1.