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: