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

User · Answer

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

User · Answer

You can try using set operations  MINUS and INTERSECT  See here for more details  O Reilly - Mastering Oracle SQL - Chapter 7 - Set Operations

User · Answer

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

User · Answer

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

User · Answer

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     Select  Tools  Select  Database Diff  Select  Source Connection  Select  Destination Connection  Select the  Standard Object Types  you want to compare Enter the  Table Name  Click  Next  until you reach  Finish  Click  Finish  NOTE  In steps 3  amp  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       lt  gt  b data type     or         a data length     lt  gt  b data length   or         a data scale      lt  gt  b data scale    or         a data precision  lt  gt  b data precision        and a column name   b column name

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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     No external tool  No performance issues with union all

User · Answer

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

User · Answer

Fast solution   SELECT   FROM TABLE1 MINUS SELECT   FROM TABLE2   No records should show

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

Examples related to oracle

Examples related to diff