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
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
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