如何在oracle中逐列比较两个表

I have two similar tables in oracle in two different databases. For example : my table name is EMPLOYEE and primary key is employee id. The same table with same columns(say 50 columns are is avlbl in two databases and two databases are linked.

我在两个不同的数据库中有两个类似的oracle表。例如：我的表名是EMPLOYEE，主键是员工ID。具有相同列的相同表（例如50列是两个数据库中的avlbl和两个数据库链接。

I want to compare these two tables column by column and find out which records are not matching. i want the specific column in each row in two tables that are not matching.

我想逐列比较这两个表，找出哪些记录不匹配。我想要两个表中不匹配的每一行中的特定列。

6 个解决方案

#1

select *
from 
(
( select * from TableInSchema1
  minus 
  select * from TableInSchema2)
union all
( select * from TableInSchema2
  minus
  select * from TableInSchema1)
)

should do the trick if you want to solve this with a query

如果你想用一个查询来解决这个问题，应该这样做

#2

As an alternative which saves from full scanning each table twice and also gives you an easy way to tell which table had more rows with a combination of values than the other:

作为一种替代方案，可以节省两次完全扫描每个表的时间，并且还可以让您轻松地告诉哪个表的行具有多个值，而不是另一个表：

SELECT col1
     , col2
     -- (include all columns that you want to compare)
     , COUNT(src1) CNT1
     , COUNT(src2) CNT2
  FROM (SELECT a.col1
             , a.col2
             -- (include all columns that you want to compare)
             , 1 src1
             , TO_NUMBER(NULL) src2
          FROM tab_a a
         UNION ALL
        SELECT b.col1
             , b.col2
             -- (include all columns that you want to compare)
             , TO_NUMBER(NULL) src1
             , 2 src2
          FROM tab_b b
       )
 GROUP BY col1
        , col2
HAVING COUNT(src1) <> COUNT(src2) -- only show the combinations that don't match

Credit goes here: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1417403971710

信用证来到这里：http：//asktom.oracle.com/pls/apex/f？p = 100：11：0 ::：：P11_QUESTION_ID：1417403971710

#3

Try to use 3rd party tool, such as SQL Data Examiner which compares Oracle databases and shows you differences.

尝试使用第三方工具，例如SQL Data Examiner，它比较Oracle数据库并显示差异。

#4

It won't be fast, and there will be a lot for you to type (unless you generate the SQL from user_tab_columns), but here is what I use when I need to compare two tables row-by-row and column-by-column.

它不会很快，你可以输入很多东西（除非你从user_tab_columns生成SQL），但是当我需要逐行和逐列地比较两个表时，我会使用柱。

The query will return all rows that

该查询将返回所有行

• Exists in table1 but not in table2
• 表1中存在但不存在于表2中
• Exists in table2 but not in table1
• 表2中存在但不存在于表1中
• Exists in both tables, but have at least one column with a different value
• 两个表中都存在，但至少有一列具有不同的值

(common identical rows will be excluded).

（将排除常见的相同行）。

"PK" is the column(s) that make up your primary key. "a" will contain A if the present row exists in table1. "b" will contain B if the present row exists in table2.

“PK”是构成主键的列。如果table1中存在当前行，则“a”将包含A.如果table2中存在当前行，则“b”将包含B.

select pk
      ,decode(a.rowid, null, null, 'A') as a
      ,decode(b.rowid, null, null, 'B') as b
      ,a.col1, b.col1
      ,a.col2, b.col2
      ,a.col3, b.col3
      ,...
  from table1 a 
  full outer 
  join table2 b using(pk)
 where decode(a.col1, b.col1, 1, 0) = 0
    or decode(a.col2, b.col2, 1, 0) = 0
    or decode(a.col3, b.col3, 1, 0) = 0
    or ...;

Edit Added example code to show the difference described in comment. Whenever one of the values contains NULL, the result will be different.

编辑添加的示例代码以显示注释中描述的差异。只要其中一个值包含NULL，结果就会不同。

with a as(
   select 0    as col1 from dual union all
   select 1    as col1 from dual union all
   select null as col1 from dual
)
,b as(
   select 1    as col1 from dual union all
   select 2    as col1 from dual union all
   select null as col1 from dual
)   
select a.col1
      ,b.col1
      ,decode(a.col1, b.col1, 'Same', 'Different') as approach_1
      ,case when a.col1 <> b.col1 then 'Different' else 'Same' end as approach_2       
  from a,b
 order 
    by a.col1
      ,b.col1;    




col1   col1_1   approach_1  approach_2
====   ======   ==========  ==========
  0        1    Different   Different  
  0        2    Different   Different  
  0      null   Different   Same         <--- 
  1        1    Same        Same       
  1        2    Different   Different  
  1      null   Different   Same         <---
null       1    Different   Same         <---
null       2    Different   Same         <---
null     null   Same        Same       

#5

Using the minus operator was working but also it was taking more time to execute which was not acceptable. I have a similar kind of requirement for data migration and I used the NOT IN operator for that. The modified query is :

使用减号运算符正在运行，但它也需要更多时间来执行，这是不可接受的。我对数据迁移有类似的要求，我使用了NOT IN运算符。修改后的查询是：

select * 
from A 
where (emp_id,emp_name) not in 
   (select emp_id,emp_name from B) 
   union all 
select * from B 
where (emp_id,emp_name) not in 
   (select emp_id,emp_name from A); 

This query executed fast. Also you can add any number of columns in the select query. Only catch is that both tables should have the exact same table structure for this to be executed.

此查询执行得很快。您还可以在选择查询中添加任意数量的列。只有catch表明两个表应该具有完全相同的表结构才能执行。

#6

SELECT *
  FROM (SELECT   table_name, COUNT (*) cnt
            FROM all_tab_columns
           WHERE owner IN ('OWNER_A')
        GROUP BY table_name) x,
       (SELECT   table_name, COUNT (*) cnt
            FROM all_tab_columns
           WHERE owner IN ('OWNER_B')
        GROUP BY table_name) y
 WHERE x.table_name = y.table_name AND x.cnt <> y.cnt;