Oracle diff: how to compare two tables?

Question

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

What is the best way to distinguish between these two tables?

Answer 1

Try the following:

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

There is no external tool. No performance issues with union all .

Answer 2

You can try using the given operations: MINUS and INTERSECT

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

Answer 3

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

Another approach would be to unload the tables into a text file and use a comparison tool such as WinMerge . With this approach, you can use complex SQL to turn tables into the same layout first.

Answer 4

On this issue, I think that you need to be very specific about what you are looking for, since there are many ways to interpret it and many different approaches. Some approaches will be too large if your question does not justify this.

At the simplest level, there is “Are the table data exactly the same or not?”, Which you can try to answer by simply comparing the counters before moving on to something more complex.

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

If you really want to synchronize table A with table B, this can be relatively simple using the MERGE command.

Answer 5

Fast decision:

 SELECT * FROM TABLE1 MINUS SELECT * FROM TABLE2 

No entries should be displayed ...

Answer 6

If you have the money to spend, use the PowerDIFF tool for Oracle: http://www.orbit-db.com . It comes with several comparison options and does an excellent job with these types of tasks.

Answer 7

You can try dbForge Data Compare for Oracle , a free graphical interface for comparing and synchronizing data that can perform these actions throughout the database or in part.

Answer 8

In addition to some of the other answers provided, if you want to look at the differences in the structure of a table with a table that may have a similar but different structure, you can do this in several ways:

The first one. If you are using Oracle SQL Developer, you can run a description of both tables to compare them:

 descr TABLE_NAME1 descr TABLE_NAME2 

The second one . The first solution may not be suitable for large tables with a large number of columns. If you want to see only differences in data between two tables, then, as mentioned by several others, you should use the SQL Minus statement.

The third. If you are using Oracle SQL Developer and want to compare the table structure of two tables using different schemas, you can do the following:

1. Select "Tools"
2. Select "Diff Database"
3. Select "Connect to Source"
4. Select Destination Connection
5. Select the "Standard Object Types" you want to compare.
6. Enter "Table Name"
7. Click Next until you reach Done.
8. Click Finish
9. NOTE. In steps 3 and 4, you must select various schemes in which there are objects that you want to compare.

Fourth, if the tables of two tables that you want to compare have more columns, are in the same schema, there is no need to compare more than two tables and are not attractive for visual comparison using the DESCR command, you can use the following to compare differences in table structure:

 select a.column_name || ' | ' || b.column_name, a.data_type || ' | ' || b.data_type, a.data_length || ' | ' || b.data_length, a.data_scale || ' | ' || b.data_scale, a.data_precision || ' | ' || b.data_precision from user_tab_columns a, user_tab_columns b where a.table_name = 'TABLE_NAME1' and b.table_name = 'TABLE_NAME2' and ( a.data_type <> b.data_type or a.data_length <> b.data_length or a.data_scale <> b.data_scale or a.data_precision <> b.data_precision ) and a.column_name = b.column_name; 

Answer 9

select * from table1 where table1.col1 in (select table2.col1 from table2)

Assuming col1 is the primary key column, and this will give all the rows in table1 matching the column of table2 .

select * from table1 where table1.col1 not in (select table2.col1 from table2)

Hope this helps

Answer 10

Try:

 select distinct T1.id from TABLE1 T1 where not exists (select distinct T2.id from TABLE2 T2 where T2.id = T1.id) 

With sql oracle 11g +

Answer 11

I used the Oracle SQL developer to export tables to CSV format and then compared using WinMerge.

Answer 12

Try this,

change session set "_convert_set_to_join" = true;

Another alternative is to manually rewrite the SQL query [replacing the minus operator with the NOT IN subquery] indicates an improvement in execution time of about 30%.

  select * from A where (col1,col2,?) not in (select col1,col2,? from B) union all select * from B where (col1,col2,?) not in (select col1,col2,? from A); 

I referenced this post, click here