Table "Diff" in Oracle

Question

What is the best way to perform a "diff" between two structurally identical tables in Oracle? they're in two different schemas (visible to each other).

Thanks,

Answer 1

If you don't have a tool like PLSQL developer, you could full outer join the two tables. If they have a primary key, you can use that in the join. This will give you an instant view on records missing in either table. Then, for the records that do exist in both tables, you can compare each of the fields. You should note that you cannot compare null with the regular = operator, so checking is table1.field1 = table2.field1 will return false if both fields are null. So you'll have to check for each field if it has the same value as in the other table, or if both are null.

Your query might look like this (to return records that don't match):

select
  *
from
  table1 t1
  full outer join table2 t2 on t2.id = t1.id
where
  -- Only one record exists
  t1.id is null or t2.id is null or
  ( -- The not = takes care of one of the fields being null
    not (t1.field1 = t2.field1) and 
    -- and they cannot both be null
    (t1.field1 is not null or t2.field1 is not null)
  )

You will have to copy that field1 condition for each of your fields. Of course you could write a function to compare field data to make your query easier, but you should keep in mind that that might decrease performance dramatically when you need to compare two large tables.

If your tables do not have a primary key, you will need to cross join them and perform these checks for each resulting record. You may speed that up a little by using full outer join on each mandatory field, because that cannot be null and can therefor be used in the join.

Answer 2

Assuming you want to compare the data (diff on entire rows) in the two tables:

  SELECT *
    FROM (SELECT 's1.t' "Row Source", a.*
            FROM (SELECT col1, col2
                    FROM s1.t tbl1
                  MINUS
                  SELECT col1, col2
                    FROM s2.t tbl2) a
          UNION ALL
          SELECT 's2.t', b.*
            FROM (SELECT col1, col2
                    FROM s2.t tbl2
                  MINUS
                  SELECT col1, col2
                    FROM s1.t tbl1) b)
ORDER BY 1;

More info about comparing two tables.