Two tips for Oracle data comparison

We use the comparison of two Oracle tables to illustrate the comparison of Oracle data. The Oracle data comparison between the two tables can be done in a frequently used way, that is, the manual method, to compare the data or through SQL statements, the two methods have their own strengths.

In some cases, we need to compare whether the data of the two tables is the same.

Assume that two tables A and B have the same fields, but the Oracle data may be different, and the data of some fields may be empty.

Method 1 (manual ):

Sort the data of the two tables to be compared and export the data to xls according to certain rules. Here, I will export the data directly using PL/SQL Developer. Then convert the xls file to a txt file. Here I use the batconv conversion formula. Now we have two txt files. Then compare the content with the beyond compare file comparator.

Advantages: Clear Oracle Data differences

Disadvantage: time-consuming.

Method 2 (comparison using SQL statements ):

Statement for creating a table:

 
 

  
  
create table formula(  
  
  
id varchar2(50) primary key  
 
 

Automatically generated, unique.

 
 

  
  
formulaName varchar2(50),  
  
  
formulaContent varchar2(2000),  
  
  
formulaType varchar2(20),  
  
  
) 
 
 

Except that the id is not empty, the other three items can be empty.

In oracle, if A field in both tables is null, it cannot be compared through A. * = B. Through the preceding SQL statement, we can find the data that exists in Table A, but does not exist in Table B. In turn, we can query the data that exists in Table B but does not exist in table. If both queries are empty, the corresponding fields of the two tables are identical.

If the two tables are in different databases, you must create a DBLink.

Advantage: Oracle Data is faster, especially when the data volume is large, the advantage is more obvious,

Disadvantage: the differences between the two tables cannot be quickly seen during comparison. Because only one of the tables is queried.