DBMS_COMPARISON Package

DBMS_COMPARISON is a new package introduced by oracle in database 11g which is used for comparing database objects in different databases.

The DBMS_COMPARISON package can compare the following types of database objects:
    a- Tables
    b- Single-table views
    c- Materialized views
    d- Synonyms for tables, single-table views, and materialized views

The DBMS_COMPARISON package cannot compare data in columns of the following data types:
    a- LONG
    b- LONG RAW
    c- ROWID
    d- UROWID
    e- CLOB
    f- NCLOB
    g- BLOB
    h- BFILE
    i- User-defined types (including object types, REFs, varrays, and nested tables)
    j- Oracle-supplied types (including any types, XML types, spatial types, and media types)



These is the steps to compare a database object that is shared at two different databases
1-Run the CREATE_COMPARE procedure in this package to create a comparison.
 
2-Run the COMPARE function in this package to compare the database object at the two databases and identify differences. This function returns TRUE when no differences are found and FALSE when differences are found. This function also populates data dictionary views with comparison results. 
Separate comparison results are generated for each execution of the COMPARE function.

Note that you can run COMPARE function at anytime after running CREATE_COMPARE procedure, each time you run COMPAREfunction it records comparison results in appropriate data dictionary views.
 
3-Use the below Data Dictionary views to examine the comparison results
                    DBA_COMPARISON
        USER_COMPARISON

        DBA_COMPARISON_COLUMNS
        USER_COMPARISON_COLUMNS

        DBA_COMPARISON_SCAN
        USER_COMPARISON_SCAN

        DBA_COMPARISON_SCAN_VALUES
        USER_COMPARISON_SCAN_VALUES

        DBA_COMPARISON_ROW_DIF
        USER_COMPARISON_ROW_DIF

4- If there are differences, and you want to synchronize the database objects at the two databases, then run the CONVERGEprocedure in this package.

5- DBMS_COMPARISON supplied another subprograms
DROP_COMPARISON Procedure  : Drops a comparison

PURGE_COMPARISON Procedure : Purges the comparison results, or a subset of the comparison results, for a comparison

RECHECK Function : Rechecks the differences in a specified scan for a comparison

Demo

Let's now create a demo regarding to the previous steps.
I will compare two database objects in different schema at the same database, If they are in different databases then you should create database link.

Suppose I have two schema ( MCIT_CMS , MCIT_CMS_2) which they have EMPLOYEES_VIEW table.
Note the table must have the same structure otherwise you will get the following exception

ORA-23625: Table shapes of MCIT_CMS.EMPLOYEES_VIEW and MCIT_CMS_2.EMPLOYEES_VIEW@ did not match. 

1- Create Comparison

 BEGIN  
   DBMS_COMPARISON.  
   CREATE_COMPARISON (comparison_name   => 'demo_comparison',  
            schema_name     => 'MCIT_CMS',  
            object_name     => 'EMPLOYEES_VIEW',  
            dblink_name     =>  NULL,  
            remote_schema_name  => 'MCIT_CMS_2',  
            remote_object_name  => 'EMPLOYEES_VIEW');  
 END;  

Note I passed dblink_name parameter as null value because two schema are in the same database.

2- Run COMPARE function

 DECLARE  
   has_no_difference  BOOLEAN;  
   scan_info      DBMS_COMPARISON.COMPARISON_TYPE;  
 BEGIN  
   has_no_difference :=  
    DBMS_COMPARISON.  
    COMPARE (comparison_name  => 'demo_comparison',  
         scan_info     => scan_info,  
         perform_row_dif  => TRUE);  
   DBMS_OUTPUT.PUT_LINE ('Scan ID: ' || scan_info.scan_id);  
   
   IF has_no_difference = TRUE  
   THEN  
    DBMS_OUTPUT.PUT_LINE ('No differences were found.');  
   ELSE  
    DBMS_OUTPUT.PUT_LINE ('Differences were found.');  
   END IF;  
 END;  

It prints "Differences were found." in DBMS console if changes were existed. and it take unique Scan Id in my database it is 3, it may take different number at your database.

3- Query comparison data dictionary views.

 SELECT * FROM USER_COMPARISON_SCAN;  
   
 SELECT * FROM USER_COMPARISON_SCAN_VALUES;  
   
 SELECT * FROM USER_COMPARISON_ROW_DIF;  

4- Synchronize the differences between two objects

You should change SCAN_ID number with your generated SCAN_ID from previous step in your database(Number inred color).

 DECLARE  
   SCAN_INFO  DBMS_COMPARISON.COMPARISON_TYPE;  
 BEGIN  
   DBMS_COMPARISON.  
   CONVERGE (COMPARISON_NAME  => 'demo_comparison',  
        SCAN_ID      => 3,  
        SCAN_INFO     => SCAN_INFO,  
        CONVERGE_OPTIONS  => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS);  
   DBMS_OUTPUT.  
   PUT_LINE ('Local Rows Merged = ' || SCAN_INFO.LOC_ROWS_MERGED);  
   DBMS_OUTPUT.  
   PUT_LINE ('Remote Rows Merged = ' || SCAN_INFO.RMT_ROWS_MERGED);  
   DBMS_OUTPUT.  
   PUT_LINE ('Local Rows Deleted = ' || SCAN_INFO.LOC_ROWS_DELETED);  
   DBMS_OUTPUT.  
   PUT_LINE ('Remote Rows Deleted = ' || SCAN_INFO.RMT_ROWS_DELETED);  
 END;  

5- Drop Comparison

 BEGIN  
   DBMS_COMPARISON.DROP_COMPARISON ('demo_comparison');  
 END;  

6- Purge Comparison

 BEGIN  
   DBMS_COMPARISON.PURGE_COMPARISON ('demo_comparison');  
 END;