[oracle] oracle diff: how to compare two tables?

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?

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.

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+

Fast solution:

SELECT * FROM TABLE1
MINUS
SELECT * FROM TABLE2

No records should show...

You can try using set operations: MINUS and INTERSECT

See here for more details:
O'Reilly - Mastering Oracle SQL - Chapter 7 - Set Operations

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.

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

Assuming col1 is the primary key column and this will give all rows in table1 respective to the table2 column 1.

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

Hope this helps

Try This,

alter session set "_convert_set_to_join"= true;

The other alternative is to rewrite the SQL query manually [replacing the minus operator with a NOT IN subquery] evidences about 30% improvement in execution time .

 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 have referred from this post click here

In addition to some of the other answers provided, if you wanted to look at the differences in table structure with a table that might have the similar but differing structure, you could do this in multiple ways:

First - If using Oracle SQL Developer, you could run a describe on both tables to compare them:

descr TABLE_NAME1
descr TABLE_NAME2

Second - The first solution may not be ideal for larger tables with a lot of columns. If you only want to see the differences in the data between the two tables, then as mentioned by several others, using the SQL Minus operator should do the job.

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

1. Select "Tools"
2. Select "Database Diff"
3. Select "Source Connection"
4. Select "Destination Connection"
5. Select the "Standard Object Types" you want to compare
6. Enter the "Table Name"
7. Click "Next" until you reach "Finish"
8. Click "Finish"
9. NOTE: In steps 3 & 4 is where you would select the differing schemas in which the objects exist that you want to compare.

Fourth - If the tables two tables you wish to compare have more columns, are in the same schema, have no need to compare more than two tables and are unappealing to compare visually using the DESCR command you can use the following to compare the differences in the 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;

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.

I used Oracle SQL developer to export the table/s into CSV format and then did the comparison using WinMerge.

Below is my solution - taking into account that the diffed tables can have duplicate rows. The accepted answer does not take this into account which would give you wrong results in case of duplicates. I am taking care of duplicate rows by numbering them using row_number() and then comparing the numbered rows:

-- TEST TABLES
create table t1 (col_num number,col_date date,col_varchar varchar2(400));
create table t2 (col_num number,col_date date,col_varchar varchar2(400));  

-- TEST DATA
insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am in both');
insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am in both');

insert into t1 values (null,null,'I am in both with nulls');
insert into t2 values (null,null,'I am in both with nulls');

insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am in T1 only');
insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am in T2 only');

insert into t1 values (null,null,'I am in T1 only with nulls');
insert into t2 values (null,null,'I am in T2 only with nulls');

insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T1 but not in T2');
insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T1 but not in T2');

insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T2 but not in T1');
insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T2 but not in T1');

insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T1 and once in T2');
insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T1 and once in T2');
insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T1 and once in T2');

insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T2 and once in T1');
insert into t2 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T2 and once in T1');
insert into t1 values (1,TO_DATE ('01.JAN.3000 00:00:00', 'DD.MON.YYYY HH24:MI:SS'),'I am twice in T2 and once in T1');

-- THE DIFF
-- All columns need to be named in the partition by clause, it is not possible to just say 'partition by *'
-- The column used in the order by clause does not matter in terms of functionality 
(
    select 'In T1 but not in T2' diff,s.* from (
        select row_number() over (partition by col_num,col_date,col_varchar order by col_num) rn,t.* from t1 t
        minus
        select row_number() over (partition by col_num,col_date,col_varchar order by col_num) rn,t.* from t2 t
    ) s
) union all (
    select 'In T2 but not in T1' diff,s.* from (
        select row_number() over (partition by col_num,col_date,col_varchar order by col_num) rn,t.* from t2 t
        minus
        select row_number() over (partition by col_num,col_date,col_varchar order by col_num) rn,t.* from t1 t
    ) s
);