Tutorial: Comparing Data in Two Different Databases

This example continues the scenario described in "Tutorial: Preparing to Compare and Converge Data". Complete the steps in that topic before continuing.

You can use the CREATE_COMPARISON procedure in the DBMS_COMPARISON package to define a comparison of a shared database object at two different databases. Once the comparison is defined, you can use the COMPARE function in this package to compare the database object specified in the comparison at the current point in time. You can run the COMPARE function multiple times for a specific comparison. Each time you run the function, it results one or more scans of the database objects, and each scan has its own scan ID.

To compare the entire hr.departments table at the ii1.example.com and ii2.example.com databases: 

1. On a command line, open SQL*Plus and connect to the ii1.example.com database as the administrative user who owns the database link created in "Tutorial: Preparing to Compare and Converge Data". For example, if SYSTEM user owns the database link, then connect as SYSTEM user:

   sqlplus system@ii1.example.com
   Enter password: password
   

   See Starting SQL*Plus and Connecting to the Database for more information about starting SQL*Plus.

2. Run the CREATE_COMPARISON procedure to create the comparison for the hr.departments table:

   BEGIN
     DBMS_COMPARISON.CREATE_COMPARISON(
       comparison_name => 'compare_departments',
       schema_name     => 'hr',
       object_name     => 'departments',
       dblink_name     => 'ii2.example.com');
   END;
   /
   

   Note that the name of the new comparison is compare_departments. This comparison is owned by the user who runs the CREATE_COMPARISON procedure.

3. Run the COMPARE function to compare the hr.departments table at the two databases:

   SET SERVEROUTPUT ON
   DECLARE
     consistent   BOOLEAN;
     scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
   BEGIN
     consistent := DBMS_COMPARISON.COMPARE(
                     comparison_name => 'compare_departments',
                     scan_info       => scan_info,
                     perform_row_dif => TRUE);
     DBMS_OUTPUT.PUT_LINE('Scan ID: '||scan_info.scan_id);
     IF consistent=TRUE THEN
       DBMS_OUTPUT.PUT_LINE('No differences were found.');
     ELSE
       DBMS_OUTPUT.PUT_LINE('Differences were found.');
     END IF;
   END;
   /
   
   Scan ID: 1
   Differences were found.
    
   PL/SQL procedure successfully completed.
   

   Specify the name of the comparison created in Step 2 for the comparison_name parameter.

   The function prints the scan ID for the comparison. The scan ID is important when you are querying data dictionary views for information about the comparison and when you are converging the database objects.

   The function also prints whether or not differences were found in the table at the two databases:

   • If the function prints 'No differences were found', then the table is consistent at the two databases.

   • If the function prints 'Differences were found', then the table has diverged at the two databases.

4. Make a note of the scan ID returned by the function in the previous step. In this example, assume the scan ID is 1.

5. If differences were found in Step 3, then run the following query to show the number of differences found:

   COLUMN OWNER HEADING 'Comparison Owner' FORMAT A16
   COLUMN COMPARISON_NAME HEADING 'Comparison Name' FORMAT A20
   COLUMN SCHEMA_NAME HEADING 'Schema Name' FORMAT A11
   COLUMN OBJECT_NAME HEADING 'Object Name' FORMAT A11
   COLUMN CURRENT_DIF_COUNT HEADING 'Differences' FORMAT 9999999
    
   SELECT c.OWNER, 
          c.COMPARISON_NAME, 
          c.SCHEMA_NAME, 
          c.OBJECT_NAME, 
          s.CURRENT_DIF_COUNT 
     FROM DBA_COMPARISON c, DBA_COMPARISON_SCAN s
     WHERE c.COMPARISON_NAME = s.COMPARISON_NAME AND
           c.OWNER           = s.OWNER AND
           s.SCAN_ID         = 1;
   

   Specify the scan ID you recorded in Step 4 in the WHERE clause of the query.

   The output will be similar to the following:

   Comparison Owner Comparison Name      Schema Name Object Name Differences
   ---------------- -------------------- ----------- ----------- -----------
   SYSTEM           COMPARE_DEPARTMENTS  HR          DEPARTMENTS           3
   

6. To see which rows were different in the database object being compared, run the following query:

   COLUMN COLUMN_NAME HEADING 'Index Column' FORMAT A15
   COLUMN INDEX_VALUE HEADING 'Index Value' FORMAT A15
   COLUMN LOCAL_ROWID HEADING 'Local Row Exists?' FORMAT A20
   COLUMN REMOTE_ROWID HEADING 'Remote Row Exists?' FORMAT A20
    
   SELECT c.COLUMN_NAME,
          r.INDEX_VALUE, 
          DECODE(r.LOCAL_ROWID,
                   NULL, 'No',
                         'Yes') LOCAL_ROWID,
          DECODE(r.REMOTE_ROWID,
                   NULL, 'No',
                         'Yes') REMOTE_ROWID
     FROM DBA_COMPARISON_COLUMNS c,
          DBA_COMPARISON_ROW_DIF r,
          DBA_COMPARISON_SCAN s
     WHERE c.COMPARISON_NAME = 'COMPARE_DEPARTMENTS' AND
           r.SCAN_ID         = s.SCAN_ID AND
           s.PARENT_SCAN_ID  = 1 AND
           r.STATUS          = 'DIF' AND
           c.INDEX_COLUMN    = 'Y' AND
           c.COMPARISON_NAME = r.COMPARISON_NAME AND
           c.OWNER           = r.OWNER
     ORDER BY r.INDEX_VALUE;
   

   In the WHERE clause, specify the name of the comparison and the scan ID for the comparison. In this example, the name of the comparison is compare_departments and the scan ID is 1.

   The output will be similar to the following:

   Index Column    Index Value     Local Row Exists?    Remote Row Exists?
   --------------- --------------- -------------------- --------------------
   DEPARTMENT_ID   10              Yes                  Yes
   DEPARTMENT_ID   270             Yes                  No
   DEPARTMENT_ID   280             No                   Yes
   

   This output shows the index column for the table being compared and the index value for each row that is different in the shared database object. In this example, the index column is the primary key column for the hr.departments table (department_id). The output also shows the type of difference for each row:

   • If Local Row Exists? and Remote Row Exists? are both Yes for a row, then the row exists in both instances of the database object, but the data in the row is different.

   • If Local Row Exists? is Yes and Remote Row Exists? is No for a row, then the row exists in the local database object but not in the remote database object.

   • If Local Row Exists? is No and Remote Row Exists? is Yes for a row, then the row exists in the remote database object but not in the local database object.