Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2002

    Question Unanswered: 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.

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: comparing tables

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


    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts