oracle diff: how to compare two tables?

Question

Suppose I have two tables, t1 and t2 which are identical in layout but which may contain different data.

What's the best way to diff these two tables?

Answer 1

You can try using set operations: MINUS and INTERSECT

See here for more details: http://oreilly.com/catalog/mastorasql/chapter/ch07.html

Answer 2

You can use a tool like AQT to create diffs between tables.

Another approach would be to dump the tables to a text file and use a diff tool like WinMerge. With this approach, you can use complex SQL to turn the tables into the same layout, first.

Answer 3

For this kind of question I think you have to be very specific about what you are looking for, as there are many ways of interpreting it and many different approaches. Some approaches are going to be too big a hammer if your question does not warrant it.

At the simplest level, there is "Is the table data exactly the same or not?", which you might attempt to answer with a simple count comparison before moving on to anything more complex.

At the other end of the scale there is "show me the rows from each table for which there is not an equivalent row in the other table" or "show me where rows have the same identifying key but different data values".

If you actually want to sync Table A with Table B then that might be relatively straightforward, using a MERGE command.

Answer 4

If you have some money to spend, use the tool PowerDIFF for Oracle: http://www.orbit-db.com. It comes with a number of comparison options and does these types of jobs excellently.

Answer 5

Try this:

(select * from T1 minus select * from T2) -- all rows that are in T1 but not in T2
union all
(select * from T2 minus select * from T1)  -- all rows that are in T2 but not in T1
;

Answer 6

You may try dbForge Data Compare for Oracle, a **free GUI tool for data comparison and synchronization, that can do these actions over all database or partially.