How to find differences between Oracle production and test envs

Determining why things behave differently in production versus a test environment can be time-consuming and painful.

That is until you realize there is a convenient package that allows you to compare the two environments quickly.

Introduced in Oracle Database 11g, the DBMS_COMPARISON package allows you to compare objects, schemas, or data between two databases or schemas (within the same database) to find where they differ.

Let’s take a closer look at how this works.

Create the Comparison

First, you must create a comparison using the DBMS_COMPARISON.CREATE_COMPARISON procedure. You will need to supply a unique name for the Comparison, the schema name, and, optionally, the object name in the current database you wish to compare. You must also supply the location of the other schema you want to compare. The comparison schema can be in the same database (specify NULL for DBLINK_NAME). Still, in my case, below, I’m comparing our product system to our test system, which is reachable from production via a database link called ‘orcl2_test’.

BEGIN
     dbms_comparison.create_comparison(
      					  comparison_name => 'COMP_SALES',
      					  schema_name     => 'SH',
      					  object_name     => 'SALES',
      					  dblink_name     => 'orcl2_test'
      					  );
END;
/  
PL/SQL PROCEDURE successfully completed.

Note: You need a unique index on both tables to compare tables, as shown above.

Run the Comparison

Now we have created the Comparison; we can execute it using the DBMS_COMPARISON.COMPARE function. The COMPARE function returns a BOOLEAN, indicating whether the objects are consistent. It returns TRUE when no differences are found and FALSE when discrepancies are found.

However, the details of the differences are not returned by the function. Instead, the differences are stored in dictionary tables; user_comparison_columns, user_comparison_row_dif, and user_comparison_scan. If a discrepancy is found, you can find details on the differences by querying these tables using the scan ID, which is returned in the variable scan_info.

How you call the COMPARE function is a little more complex than a standard PL/SQL function call. I’ve wrapped the function call in an IF NOT statement in the example below. If the DBMS_COMPARISON.COMPARE function returns FALSE (IF NOT FALSE = TRUE), the function will print out the SCAN ID, so we can use it to query the dictionary table. If, on the other hand, the function returns TRUE (IF NOT TRUE = FALSE), it will return nothing.

DECLARE
        scan_info DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
         IF NOT DBMS_COMPARISON.COMPARE
       		     ( comparison_name => 'COMP_SALES'
       		     , scan_info       => scan_info
       		     , perform_row_dif => TRUE
       		     ) THEN
           DBMS_OUTPUT.PUT_LINE('Scan ID:'||scan_info.scan_id);
        END IF;
END;
 /
 
Scan ID:3

Find the details on the Comparison

As you can see, there must be a discrepancy between our production and test environments, as the COMPARE function returned a SCAN ID of 3. We need to query the dictionary tables using the comparison name and the scan id to determine the difference. Below is the query we need to use. You will notice I’ve included two case statements to indicate if the difference was found on the local database (in this case, production) or on the Remote database (in this case, our test environment).

SELECT c.column_name, r.index_value, 
       CASE WHEN r.local_rowid IS NULL  THEN 'No' ELSE 'Yes' END  LOC, 
       CASE WHEN r.remote_rowid IS NULL THEN 'No' ELSE 'Yes' END REM 
  2  FROM   user_comparison_columns c, user_comparison_row_dif r, user_comparison_scan s
  3  WHERE  c.comparison_name = 'COMP_SALES'
  4  AND    r.scan_id         = s.scan_id
  5  AND    r.status	      = 'DIF'
  6  AND    c.index_column    = 'Y'
  7  AND    c.comparison_name = r.comparison_name
  8  AND    s.scan_id         = 3  
  9  ORDER BY r.index_value;
 
COLUMN_NAME    INDEX_VALUE    LOC    REM    
______________ ______________ ______ ______ 
ID             5000           Yes    Yes

The result of our query shows a difference in the data that occurs in the row with ID 5000. However, it doesn’t show us which column has the problem. I must extract the row with ID=5000 from both systems and manually compare them.

SELECT * FROM sales WHERE id=5000;
 
ORDER_ID        CUST_ID    PRODUCT_ID    SUPPLIER_ID     DATE_ID     AMOUNT_SOLD     PRICE     TAX_CODE         ID                                                                                     
____________ ___________ _____________ ______________ ____________ ______________ ___________ __________ ___________  
   248173057    25162649        610090        1229054  18-JAN-23                10    140          .5          5000 
 
SELECT * FROM  sales@orcl2_test WHERE id=5000;
 
ORDER_ID        CUST_ID    PRODUCT_ID    SUPPLIER_ID     DATE_ID     AMOUNT_SOLD     PRICE     TAX_CODE         ID                                                                                       
____________ ___________ _____________ ______________ ____________ ______________ ___________ __________ ___________  
   248173057    25162649        610090        1229054  18-JAN-23                10    140        xx.xx        5000

As you can see from the output above, the difference is in the TAX_CODE column. The TAX_CODE has been masked in the test environment for this particular row.

You may be wondering why Oracle couldn’t show me the exact difference between the two tables. If I had a unique index on the TAX_CODE column, Oracle could have told me the value that differed. But since this is not a unique column, Oracle can only pinpoint the row for me using the unique index on the ID column.

This blog post is part of a series on useful PL/SQL packages. Other blogs in this series include: