Suppose you share the hr.departments table in two databases. You want to compare this table at these databases to see if their data is consistent. If the tables have diverged at the two databases, then you want to converge them to make them consistent.

Meet the following prerequisites to complete this tutorial:

• Configure network connectivity so that the two databases can communicate with each other. See Oracle Database 2 Day DBA for information about configuring network connectivity between databases.

• Ensure that the hr sample schema is installed on both databases.

In this example, the global names of the databases are ii1.example.com and ii2.example.com, but you can substitute any two databases in your environment that meet the prerequisites.

To prepare for comparison and convergence of the hr.departments table at the ii1.example.com and ii2.example.com databases:

1. For the purposes of this example, make the hr.departments table diverge at the two databases:

   1. On a command line, open SQL*Plus and connect to the ii2.example.com database as hr user.

      See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

   2. Delete the department in the hr.departments table with the department_id equal to 270:

      DELETE FROM hr.departments WHERE department_id=270;
      COMMIT;
      

   3. Modify the data in a row in the hr.departments table:

      UPDATE hr.departments SET manager_id=114 WHERE department_id=10;
      COMMIT;
      

   4. Insert a row into the hr.departments table:

      INSERT INTO hr.departments VALUES(280, 'Bean Counters', 108, 2700);
      COMMIT;
      

   5. Exit SQL*Plus:

      EXIT;
      

   Note:

   Usually, Step 1 is not required. It is included in this example to ensure that the hr.departments table diverges at the two databases.

2. Create a database link from the ii1.example.com database to the ii2.example.com database.

   The database link should connect from an administrative user in ii1.example.com to an administrative user schema in ii2.example.com. The administrative user at both databases should have the necessary privileges to access and modify the hr.departments table and the necessary privileges to run subprograms in the DBMS_COMPARISON package. If you are not sure which user has these privileges, then use SYSTEM user. Also, both the name and the service name of the database link must be ii2.example.com. See "Configuring Network Connectivity and Database Links" for more information.

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 Oracle Database 2 Day DBA 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 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.

This example continues the scenario described in "Tutorial: Comparing Data in Two Different Databases". Complete the steps in that topic before continuing.

When a shared database object has diverged at two different databases, you can use the CONVERGE procedure in the DBMS_COMPARISON package to converge the two instances of the database object. After the CONVERGE procedure runs successfully, the shared database object is consistent at the two databases. To run the CONVERGE procedure, you must specify the following information:

• The name of an existing comparison created using the CREATE_COMPARISON procedure in the DBMS_COMPARISON package

• The scan ID of the comparison that you want to converge

The scan ID contains information about the differences that will be converged. In this example, the name of the comparison is compare_departments and the scan ID is 1.

Also, when you run the CONVERGE procedure, you must specify which database "wins" when the shared database object is converged. If you specify that the local database wins, then the data in the database object at the local database replaces the data in the database object at the remote database when the data is different. If you specify that the remote database wins, then the data in the database object at the remote database replaces the data in the database object at the local database when the data is different. In this example, the local database ii1.example.com wins.

To converge divergent data in the 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 the SYSTEM user owns the database link, then connect as the SYSTEM user:

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

   See Oracle Database 2 Day DBA for more information about starting SQL*Plus.

2. Run the CONVERGE procedure to converge the hr.departments table at the two databases:

   SET SERVEROUTPUT ON
   DECLARE
     scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
   BEGIN
     DBMS_COMPARISON.CONVERGE(
       comparison_name  => 'compare_departments',
       scan_id          => 1,
       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;
   /
   
   Local Rows Merged: 0
   Remote Rows Merged: 2
   Local Rows Deleted: 0
   Remote Rows Deleted: 1
   
   PL/SQL procedure successfully completed.
   

The CONVERGE procedure synchronizes the portion of the database object compared by the specified scan and returns information about the changes it made. Some scans might compare a subset of the database object. In this example, the specified scan compared the entire table. So, the entire table is synchronized, assuming no new differences appeared after the comparison scan completed.

The local table wins in this example because the converge_options parameter is set to DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS in the procedure. That is, for the rows that are different in the two databases, the rows at the local database replace the corresponding rows at the remote database. If some rows exist at the remote database but not at the local database, then the extra rows at the remote database are deleted. If instead you want the remote database to win, then set the converge_options parameter to DBMS_COMPARISON.CMP_CONVERGE_REMOTE_WINS in the procedure.

In addition, if you run the CONVERGE procedure on a shared database object that is part of an Oracle Streams replication environment, then you might not want the changes made by the procedure to be replicated to other databases. In this case, you can set the following parameters in the CONVERGE procedure to values that will prevent the changes from being replicated:

• local_converge_tag

• remote_converge_tag

When one of these parameters is set to a non-NULL value, a tag is set in the session that makes the changes during convergence. The local_converge_tag parameter sets the tag in the session at the local database, while the remote_converge_tag parameter sets the tag in the session at the remote database. If you do not want the changes made by the CONVERGE procedure to be replicated, then set these parameters to a value that will prevent Oracle Streams capture processes and synchronous captures from capturing the changes.

The column_list parameter in the CREATE_COMPARISON procedure enables you to compare a subset of the columns in a database object. The following are reasons to compare a subset of columns:

• A database object contains extra columns that do not exist in the database object to which it is being compared. In this case, the column_list parameter must only contain the columns that exist in both database objects.

• You want to focus a comparison on a specific set of columns. For example, if a table contains hundreds of columns, then you might want to list specific columns in the column_list parameter to make the comparison more efficient.

• Differences are expected in some columns. In this case, exclude the columns in which differences are expected from the column_list parameter.

The columns in the column list must meet the following requirements:

• The column list must meet the index column requirements for the DBMS_COMPARISON package. See Oracle Database PL/SQL Packages and Types Reference for information about index column requirements.

• If you plan to use the CONVERGE procedure to make changes to a database object based on comparison results, then you must include in the column list any column in this database object that has a NOT NULL constraint but no default value.

This example compares the order_id, order_date, and customer_id columns in the oe.orders table at the comp1.example.com and comp2.example.com databases:

When you run the COMPARE procedure for an existing comparison, the perform_row_dif parameter controls whether the COMPARE procedure identifies each individual row difference in the database objects:

• When the perform_row_dif parameter is set to TRUE, the COMPARE procedure records whether there are differences in the database objects, and it records each individual row difference. Set this parameter to TRUE when you must identify each difference in the database objects.

• When the perform_row_dif parameter is set to FALSE, the COMPARE procedure records whether there are differences in the database objects, but does not record each individual row difference. Set this parameter to FALSE when you want to know if there are differences in the database objects, but you do not need to identify each individual difference. Setting this parameter to FALSE is the most efficient way to perform a comparison.

See Oracle Database PL/SQL Packages and Types Reference for information about the perform_row_dif parameter in the COMPARE function.

This example compares the entire oe.orders table at the comp1.example.com and comp2.example.com databases without identifying individual row differences:

The scan_percent and scan_mode parameters in the CREATE_COMPARISON procedure enable you to compare a random portion of a shared database object instead of the entire database object. Typically, you use this option under the following conditions:

• You are comparing a relatively large shared database object, and you want to determine whether there might be differences without devoting the resources and time to comparing the entire database object.

• You do not intend to use subsequent comparisons to compare different portions of the database object. If you want to compare different portions of the database object in subsequent comparisons, then see "Comparing a Shared Database Object Cyclically" for instructions.

This example compares a random portion of the oe.orders table at the comp1.example.com and comp2.example.com databases:

The scan_percent and scan_mode parameters in the CREATE_COMPARISON procedure enable you to compare a portion of a shared database object cyclically. A cyclic comparison scans a portion of the database object being compared during a single comparison. When the database object is compared again, another portion of the database object is compared, starting where the last comparison ended.

Typically, you use this option under the following conditions:

• You are comparing a relatively large shared database object, and you want to determine whether there might be differences without devoting the resources and time to comparing the entire database object.

• You want each comparison to compare a different portion of the shared database object, so that the entire database object is compared with the appropriate number of scans. For example, if you compare 25% of the shared database object, then the entire database object is compared after four comparisons. If you do not want to compare different portions of the database object in subsequent comparisons, see "Comparing a Random Portion of a Shared Database Object" for instructions.

This example compares oe.orders table cyclically at the comp1.example.com and comp2.example.com databases:

The scan_mode parameter in the CREATE_COMPARISON procedure enables you to compare a custom portion of a shared database object. After a comparison is created with the scan_mode parameter set to CMP_SCAN_MODE_CUSTOM in the CREATE_COMPARISON procedure, you can specify the exact portion of the database object to compare when you run the COMPARE function.

Typically, you use this option under the following conditions:

• You have a specific portion of a shared database object that you want to compare.

• You are comparing a relatively large shared database object, and you want to determine whether there might be difference in a specific portion of it without devoting the resources and time to comparing the entire database object.

See Oracle Database PL/SQL Packages and Types Reference for information about the scan_mode parameter in the CREATE_COMPARISON procedure.

This example compares a custom portion of the oe.orders table at the comp1.example.com and comp2.example.com databases:

The DBMS_COMPARISON package does not support directly comparing a shared database object that contains a column of either CLOB or BLOB data type. However, you can complete these basic steps to compare a table with a CLOB or BLOB column:

1. At each database, create a view based on the table and replace the CLOB or BLOB column with a RAW data type column that is generated using the DBMS_CRYPTO.HASH function.

2. Compare the views created in Step 1.

The illustrates how complete these steps for a simple table with a NUMBER column and a CLOB column. In this example, the global names of the databases are comp1.example.com and comp2.example.com, but you can substitute any two databases in your environment that meet the prerequisites described in "Preparing To Compare and Converge a Shared Database Object".

Complete the following steps:

1. Complete the tasks described in "Preparing To Compare and Converge a Shared Database Object".

2. At the comp1.example.com database, ensure that the user who owns or will own the table with the CLOB or BLOB column has EXECUTE privilege on the DBMS_CRYPTO package.

   In this example, assume the user who will own the table is oe. Complete the following steps to grant this privilege to oe user:

   1. In SQL*Plus, connect to the comp1.example.com database as an administrative user who can grant privileges.

   2. Grant EXECUTE on the DBMS_CRYPTO package to the user:

      GRANT EXECUTE ON DBMS_CRYPTO TO oe;
      

3. At the comp2.example.com database, ensure that the user who owns or will own the table with the CLOB or BLOB column has EXECUTE privilege on the DBMS_CRYPTO package.

   In this example, assume the user who will own the table is oe. Complete the following steps to grant this privilege to oe user:

   1. In SQL*Plus, connect to the comp2.example.com database as an administrative user who can grant privileges.

   2. Grant EXECUTE on the DBMS_CRYPTO package to the user:

      GRANT EXECUTE ON DBMS_CRYPTO TO oe;
      

4. Create the table with the CLOB column and the view based on the table in the comp1.example.com database:

   1. In SQL*Plus, connect to the comp1.example.com database as the user who will own the table.

      See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

   2. Create the table:

      CREATE TABLE oe.tab_lob(
        c1 NUMBER PRIMARY KEY, 
        c2 CLOB DEFAULT to_clob('c2'));
      

   3. Insert a row into the tab_lob table and commit the change:

      INSERT INTO oe.tab_lob VALUES(1, TO_CLOB('row 1'));COMMIT;
      

   4. Create the view:

      BEGIN
        EXECUTE IMMEDIATE 'CREATE VIEW view_lob AS SELECT 
            c1, 
            DBMS_CRYPTO.HASH(c2, '||DBMS_CRYPTO.HASH_SH1||') c2_hash 
          FROM tab_lob';
      END;
      /
      

   See Also:

   Oracle Database PL/SQL Packages and Types Reference for more information about the cryptographic hash functions used in the DBMS_CRYPTO package

5. Create the table with the CLOB column and the view based on the table in the comp2.example.com database:

   1. In SQL*Plus, connect to the comp2.example.com database as the user who will own the table.

      See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

   2. Create the table:

      CREATE TABLE oe.tab_lob(
        c1 NUMBER PRIMARY KEY, 
        c2 CLOB DEFAULT to_clob('c2'));
      

   3. Insert a row into the tab_lob table and commit the change:

      INSERT INTO oe.tab_lob VALUES(1, TO_CLOB('row 1'));COMMIT;
      

   4. Create the view:

      BEGIN
        EXECUTE IMMEDIATE 'CREATE VIEW view_lob AS SELECT 
            c1, 
            DBMS_CRYPTO.HASH(c2, '||DBMS_CRYPTO.HASH_SH1||') c2_hash 
          FROM tab_lob';
      END;
      /
      

6. In SQL*Plus, connect to the comp1.example.com database as the administrative user who owns the database link created in "Preparing To Compare and Converge a Shared Database Object".

7. Run the CREATE_COMPARISON procedure to create the comparison:

   BEGIN
     DBMS_COMPARISON.CREATE_COMPARISON(
       comparison_name => 'compare_lob',
       schema_name     => 'oe',
       object_name     => 'view_lob',
       dblink_name     => 'comp2.example.com');
   END;
   /
   

   Notice that the schema_name and object_name parameters specify the view oe.view_lob and not the table that contains the CLOB column.

8. Run the COMPARE function to compare the oe.view_lob view at the two databases:

   SET SERVEROUTPUT ON
   DECLARE
     consistent   BOOLEAN;
     scan_info    DBMS_COMPARISON.COMPARISON_TYPE;
   BEGIN
     consistent := DBMS_COMPARISON.COMPARE(
                     comparison_name => 'compare_lob',
                     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
   No differences were found.
    
   PL/SQL procedure successfully completed.
   

9. Make the oe.tab_lob table diverge at two databases by completing the following steps:

   1. In SQL*Plus, connect to the comp1.example.com database as the user who owns the table.

      See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

   2. Insert a row and commit the change:

      INSERT INTO oe.tab_lob VALUES(2, TO_CLOB('row a'));
      COMMIT;
      

   3. In SQL*Plus, connect to the comp2.example.com database as the user who owns the table.

      See Oracle Database Administrator's Guide for instructions about connecting to a database in SQL*Plus.

   4. Insert a row and commit the change:

      INSERT INTO oe.tab_lob VALUES(2, TO_CLOB('row b'));
      COMMIT;
      

10. Run the COMPARE function again to compare the oe.view_lob view at the two databases. See Step 8.

    The shared table with the CLOB column has diverged at the two databases. Therefore, when you compare the view, the COMPARE function returns the following output:

    Scan ID: 2
    Differences were found.
     
    PL/SQL procedure successfully completed.
    

The DBA_COMPARISON data dictionary view contains information about the comparisons in the local database. The query in this section displays the following information about each comparison:

• The owner of the comparison

• The name of the comparison

• The schema that contains the database object compared by the comparison

• The name of the database object compared by the comparison

• The data type of the database object compared by the comparison

• The scan mode used by the comparison. The following scan modes are possible:

  • FULL indicates that the entire database object is compared.

  • RANDOM indicates that a random portion of the database object is compared.

  • CYCLIC indicates that a portion of the database object is compared during a single comparison. When the database object is compared again, another portion of the database object is compared, starting where the last compare ended.

  • CUSTOM indicates that the COMPARE function specifies the range to compare in the database object.

• The name of the database link used to connect with the remote database

To view this information, run the following query:

COLUMN OWNER HEADING 'Comparison|Owner' FORMAT A10
COLUMN COMPARISON_NAME HEADING 'Comparison|Name' FORMAT A22
COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A8
COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A8
COLUMN OBJECT_TYPE HEADING 'Object|Type' FORMAT A8
COLUMN SCAN_MODE HEADING 'Scan|Mode' FORMAT A6
COLUMN DBLINK_NAME HEADING 'Database|Link' FORMAT A15
 
SELECT OWNER,
       COMPARISON_NAME,
       SCHEMA_NAME,
       OBJECT_NAME,
       OBJECT_TYPE,
       SCAN_MODE,
       DBLINK_NAME
  FROM DBA_COMPARISON;

Your output is similar to the following:

Comparison Comparison             Schema   Object   Object   Scan   Database
Owner      Name                   Name     Name     Type     Mode   Link
---------- ---------------------- -------- -------- -------- ------ ----------
ADMIN      COMPARE_SUBSET_COLUMNS OE       ORDERS   TABLE    FULL   COMP2.EXAM
                                                                    PLE
ADMIN      COMPARE_ORDERS         OE       ORDERS   TABLE    FULL   COMP2.EXAM
                                                                    PLE
ADMIN      COMPARE_RANDOM         OE       ORDERS   TABLE    RANDOM COMP2.EXAM
                                                                    PLE
ADMIN      COMPARE_CYCLIC         OE       ORDERS   TABLE    CYCLIC COMP2.EXAM
                                                                    PLE
ADMIN      COMPARE_CUSTOM         OE       ORDERS   TABLE    CUSTOM COMP2.EXAM
                                                                    PLE

A comparison compares the local database object with a database object at a remote database. The comparison uses the database link shown by the query to connect to the remote database and perform the comparison.

By default, a comparison assumes that the owner, name, and data type of the database objects being compared are the same at both databases. However, they can be different at the local and remote databases. The query in this section does not display information about the remote database object, but you can query the REMOTE_SCHEMA_NAME, REMOTE_OBJECT_NAME, and REMOTE_OBJECT_TYPE columns to view this information.

When you create comparisons that use the scan modes RANDOM or CYCLIC, you specify the percentage of the shared database object to compare. The query in this section shows the following information about random and cyclic comparisons:

• The owner of the comparison

• The name of the comparison

• The schema that contains the database object compared by the comparison

• The name of the database object compared by the comparison

• The data type of the database object compared by the comparison

• The scan percentage for the comparison. Each time the COMPARE function is run to perform a comparison scan, the specified percentage of the database object is compared.

• The last lead index column value used by the comparison. The next time the COMPARE function is run, it will start with row that has a lead index column value that directly follows the value shown by the query. This value only applies to cyclic comparisons.

To view this information, run the following query:

COLUMN OWNER HEADING 'Comparison|Owner' FORMAT A10
COLUMN COMPARISON_NAME HEADING 'Comparison|Name' FORMAT A22
COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A8
COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A8
COLUMN OBJECT_TYPE HEADING 'Object|Type' FORMAT A8
COLUMN SCAN_PERCENT HEADING 'Scan|Percent' FORMAT 999
COLUMN CYCLIC_INDEX_VALUE HEADING 'Cyclic|Index|Value' FORMAT A10
 
SELECT OWNER,
       COMPARISON_NAME,
       SCHEMA_NAME,
       OBJECT_NAME,
       OBJECT_TYPE,
       SCAN_PERCENT,
       CYCLIC_INDEX_VALUE
  FROM DBA_COMPARISON
  WHERE SCAN_PERCENT IS NOT NULL;

Your output is similar to the following:

                                                                     Cyclic
Comparison Comparison             Schema   Object   Object      Scan Index
Owner      Name                   Name     Name     Type     Percent Value
---------- ---------------------- -------- -------- -------- ------- ----------
ADMIN      COMPARE_RANDOM         OE       ORDERS   TABLE         50
ADMIN      COMPARE_CYCLIC         OE       ORDERS   TABLE         50 2677

When you create a comparison, you can specify that the comparison compares all of the columns in the shared database object or a subset of the columns. Also, you can specify an index for the comparison to use or let the system identify an index automatically.

The query in this section displays the following information:

• The owner of the comparison

• The name of the comparison

• The schema that contains the database object compared by the comparison

• The name of the database object compared by the comparison

• The column name of each column being compared in each database object

• The column position of each column

• Whether a column is an index column

To display this information, run the following query:

COLUMN OWNER HEADING 'Comparison|Owner' FORMAT A10
COLUMN COMPARISON_NAME HEADING 'Comparison|Name' FORMAT A15
COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A10
COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A10
COLUMN COLUMN_NAME HEADING 'Column|Name' FORMAT A12
COLUMN COLUMN_POSITION HEADING 'Column|Position' FORMAT 9999
COLUMN INDEX_COLUMN HEADING 'Index|Column?' FORMAT A7
 
SELECT c.OWNER,
       c.COMPARISON_NAME,
       c.SCHEMA_NAME,
       c.OBJECT_NAME,
       o.COLUMN_NAME,
       o.COLUMN_POSITION,
       o.INDEX_COLUMN
  FROM DBA_COMPARISON c, DBA_COMPARISON_COLUMNS o
  WHERE c.OWNER           = o.OWNER AND
        c.COMPARISON_NAME = o.COMPARISON_NAME
  ORDER BY COMPARISON_NAME, COLUMN_POSITION;

Your output is similar to the following:

Comparison Comparison      Schema     Object     Column         Column Index
Owner      Name            Name       Name       Name         Position Column?
---------- --------------- ---------- ---------- ------------ -------- -------
ADMIN      COMPARE_CUSTOM  OE         ORDERS     ORDER_ID            1 Y
ADMIN      COMPARE_CUSTOM  OE         ORDERS     ORDER_DATE          2 N
ADMIN      COMPARE_CUSTOM  OE         ORDERS     ORDER_MODE          3 N
ADMIN      COMPARE_CUSTOM  OE         ORDERS     CUSTOMER_ID         4 N
ADMIN      COMPARE_CUSTOM  OE         ORDERS     ORDER_STATUS        5 N
ADMIN      COMPARE_CUSTOM  OE         ORDERS     ORDER_TOTAL         6 N
ADMIN      COMPARE_CUSTOM  OE         ORDERS     SALES_REP_ID        7 N
ADMIN      COMPARE_CUSTOM  OE         ORDERS     PROMOTION_ID        8 N
ADMIN      COMPARE_CYCLIC  OE         ORDERS     ORDER_ID            1 Y
ADMIN      COMPARE_CYCLIC  OE         ORDERS     ORDER_DATE          2 N
ADMIN      COMPARE_CYCLIC  OE         ORDERS     ORDER_MODE          3 N
ADMIN      COMPARE_CYCLIC  OE         ORDERS     CUSTOMER_ID         4 N
ADMIN      COMPARE_CYCLIC  OE         ORDERS     ORDER_STATUS        5 N
ADMIN      COMPARE_CYCLIC  OE         ORDERS     ORDER_TOTAL         6 N
ADMIN      COMPARE_CYCLIC  OE         ORDERS     SALES_REP_ID        7 N
ADMIN      COMPARE_CYCLIC  OE         ORDERS     PROMOTION_ID        8 N
.
.
.

Each scan compares a bucket at the local database with a bucket at the remote database. The buckets being compared contain the same range of rows in the shared database object. The comparison results generated by a single execution of the COMPARE function can include multiple buckets and multiple scans. Each scan has a unique scan ID.

The query in this section shows the following information about each scan:

• The owner of the comparison that ran the scan

• The name of the comparison that ran the scan

• The schema that contains the database object compared by the scan

• The name of the database object compared by the scan

• The scan ID of the scan

• The status of the scan. The following status values are possible:

  • SUC indicates that the two buckets in the two tables matched the last time this data dictionary row was updated.

  • BUCKET DIF indicates that the two buckets in the two tables did not match. Each bucket consists of smaller buckets.

  • FINAL BUCKET DIF indicates that the two buckets in the two tables did not match. Neither bucket is composed of smaller buckets. Because the perform_row_dif parameter in the COMPARE function or the RECHECK function was set to FALSE, individual row differences were not identified for the bucket.

  • ROW DIF indicates that the two buckets in the two tables did not match. Neither bucket is composed of smaller buckets. Because the perform_row_dif parameter in the COMPARE function or the RECHECK function was set to TRUE, individual row differences were identified for the bucket.

• The number of rows compared in the scan

• The last time the scan was updated

To view this information, run the following query:

COLUMN OWNER HEADING 'Comparison|Owner' FORMAT A10
COLUMN COMPARISON_NAME HEADING 'Comparison|Name' FORMAT A15
COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A6
COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A6
COLUMN SCAN_ID HEADING 'Scan|ID' FORMAT 9999
COLUMN STATUS HEADING 'Scan|Status' FORMAT A10
COLUMN COUNT_ROWS HEADING 'Number|of|Rows' FORMAT 9999999
COLUMN SCAN_NULLS HEADING 'Scan|NULLs?' FORMAT A6
COLUMN LAST_UPDATE_TIME HEADING 'Last|Update' FORMAT A11

SELECT c.OWNER,
       c.COMPARISON_NAME,
       c.SCHEMA_NAME,
       c.OBJECT_NAME,
       s.SCAN_ID,
       s.STATUS,
       s.COUNT_ROWS,
       TO_CHAR(s.LAST_UPDATE_TIME, 'DD-MON-YYYY HH24:MI:SS') LAST_UPDATE_TIME 
  FROM DBA_COMPARISON c, DBA_COMPARISON_SCAN s
  WHERE c.OWNER           = s.OWNER AND
        c.COMPARISON_NAME = s.COMPARISON_NAME
  ORDER BY SCAN_ID;

Your output is similar to the following:

                                                            Number
Comparison Comparison      Schema Object  Scan Scan             of Last
Owner      Name            Name   Name      ID Status         Rows Update
---------- --------------- ------ ------ ----- ---------- -------- -----------
ADMIN      COMPARE_SUBSET_ OE     ORDERS     1 BUCKET DIF          20-DEC-2006
           COLUMNS                                                  09:46:34
ADMIN      COMPARE_SUBSET_ OE     ORDERS     2 ROW DIF         105 20-DEC-2006
           COLUMNS                                                  09:46:34
ADMIN      COMPARE_SUBSET_ OE     ORDERS     3 ROW DIF           1 20-DEC-2006
           COLUMNS                                                  09:46:35
ADMIN      COMPARE_ORDERS  OE     ORDERS     4 BUCKET DIF          20-DEC-2006
                                                                    09:47:02
ADMIN      COMPARE_ORDERS  OE     ORDERS     5 FINAL BUCK      105 20-DEC-2006
                                               ET DIF               09:47:02
ADMIN      COMPARE_ORDERS  OE     ORDERS     6 FINAL BUCK        1 20-DEC-2006
                                               ET DIF               09:47:02
ADMIN      COMPARE_RANDOM  OE     ORDERS     7 SUC                 20-DEC-2006
                                                                    09:47:37
ADMIN      COMPARE_CYCLIC  OE     ORDERS     8 BUCKET DIF          20-DEC-2006
                                                                    09:48:22
ADMIN      COMPARE_CYCLIC  OE     ORDERS     9 ROW DIF         105 20-DEC-2006
                                                                    09:48:22
ADMIN      COMPARE_CUSTOM  OE     ORDERS    10 BUCKET DIF          20-DEC-2006
                                                                    09:49:15
ADMIN      COMPARE_CUSTOM  OE     ORDERS    11 ROW DIF          16 20-DEC-2006
                                                                    09:49:15
ADMIN      COMPARE_CUSTOM  OE     ORDERS    12 ROW DIF          13 20-DEC-2006
                                                                    09:49:15

When a scan has a status of BUCKET DIF, FINAL BUCKET DIF, or ROW DIF, you can converge the differences found in the scan by running the CONVERGE procedure and specifying the scan ID. However, to converge the all of the rows in the comparison results instead of the portion checked in a specific scan, specify the root scan ID for the comparison results when you run the CONVERGE procedure.

Also, when a scan shows that differences were found, you can recheck the scan using the RECHECK function. To recheck all of the rows in the comparison results, run the RECHECK function and specify the root scan ID for the comparison results.

The query in this section shows the parent scan ID and root scan ID of each scan in the database. Specifically, the query shows the following information:

• The owner of the comparison that ran the scan

• The name of the comparison that ran the scan

• The schema that contains the database object compared by the scan

• The name of the database object compared by the scan

• The scan ID of the scan

• The scan ID of the scan's parent scan

• The scan ID of the scan's root scan

To view this information, run the following query:

COLUMN OWNER HEADING 'Comparison|Owner' FORMAT A10
COLUMN COMPARISON_NAME HEADING 'Comparison|Name' FORMAT A15
COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A10
COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A10
COLUMN SCAN_ID HEADING 'Scan|ID' FORMAT 9999
COLUMN PARENT_SCAN_ID HEADING 'Parent|Scan ID' FORMAT 9999
COLUMN ROOT_SCAN_ID HEADING 'Root|Scan ID' FORMAT 9999
 
SELECT c.OWNER,
       c.COMPARISON_NAME,
       c.SCHEMA_NAME,
       c.OBJECT_NAME,
       s.SCAN_ID,
       s.PARENT_SCAN_ID,
       s.ROOT_SCAN_ID
  FROM DBA_COMPARISON c, DBA_COMPARISON_SCAN s
  WHERE c.OWNER           = s.OWNER AND
        c.COMPARISON_NAME = s.COMPARISON_NAME
  ORDER BY s.SCAN_ID;

Your output is similar to the following:

Comparison Comparison      Schema     Object      Scan  Parent    Root
Owner      Name            Name       Name          ID Scan ID Scan ID
---------- --------------- ---------- ---------- ----- ------- -------
ADMIN      COMPARE_SUBSET_ OE         ORDERS         1               1
           COLUMNS
ADMIN      COMPARE_SUBSET_ OE         ORDERS         2       1       1
           COLUMNS
ADMIN      COMPARE_SUBSET_ OE         ORDERS         3       1       1
           COLUMNS
ADMIN      COMPARE_ORDERS  OE         ORDERS         4               4
ADMIN      COMPARE_ORDERS  OE         ORDERS         5       4       4
ADMIN      COMPARE_ORDERS  OE         ORDERS         6       4       4
ADMIN      COMPARE_RANDOM  OE         ORDERS         7               7
ADMIN      COMPARE_CYCLIC  OE         ORDERS         8               8
ADMIN      COMPARE_CYCLIC  OE         ORDERS         9       8       8
ADMIN      COMPARE_CUSTOM  OE         ORDERS        10              10
ADMIN      COMPARE_CUSTOM  OE         ORDERS        11      10      10
ADMIN      COMPARE_CUSTOM  OE         ORDERS        12      10      10

This output shows, for example, that the scan with scan ID 1 is the root scan in the comparison results for the COMPARE_SUBSET_COLUMNS comparison. Differences were found in this root scan, and it was split into two smaller buckets. The scan with scan ID 2 and the scan with scan ID 3 are the scans for these smaller buckets.

To see if there were differences found in a specific scan, run the query in "Viewing General Information About Each Scan in a Database". When you RECHECK for differences or CONVERGE differences in a shared database object, you specify the scan ID of the scan you want to recheck or converge. To recheck or converge all of the rows in the comparison results, specify the root scan ID for the comparison results.

The queries in this section display detailed information about the row differences found in comparison results. To view the information in the queries in this section, the perform_row_dif parameter in the COMPARE function or the RECHECK function that performed the comparison must have been set to TRUE.

If this parameter was set to FALSE, then you can query the STATUS column in the DBA_COMPARISON_SCAN view to determine whether the scan found any differences, without showing detailed information about the differences. See "Viewing General Information About Each Scan in a Database" for more information and a sample query.

The following query shows the total number of differences found for a scan with the scan ID of 8:

COLUMN OWNER HEADING 'Comparison Owner' FORMAT A16
COLUMN COMPARISON_NAME HEADING 'Comparison Name' FORMAT A25
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         = 8;

Your output is similar to the following:

Comparison Owner Comparison Name           Schema Name Object Name Differences
---------------- ------------------------- ----------- ----------- -----------
ADMIN            COMPARE_CYCLIC            OE          ORDERS                6

To view detailed information about each row difference found in the scan with scan ID 8 of the comparison results for the COMPARE_CYCLIC comparison, 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_CYCLIC' AND
        r.SCAN_ID         = s.SCAN_ID AND
        s.PARENT_SCAN_ID  = 8 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;

Your output is similar to the following:

Index Column    Index Value     Local Row Exists?    Remote Row Exists?
--------------- --------------- -------------------- --------------------
ORDER_ID        2366            Yes                  No
ORDER_ID        2385            Yes                  No
ORDER_ID        2396            Yes                  No
ORDER_ID        2425            Yes                  No
ORDER_ID        2440            Yes                  Yes
ORDER_ID        2450            Yes                  No

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 oe.orders table (order_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.

Each scan compares a range of rows in a shared database object. The query in this section provides the following information about the rows compared in each scan in the database:

• The owner of the comparison that ran the scan

• The name of the comparison that ran the scan

• The column position of the row values displayed by the query

• The minimum value for the range of rows compared by the scan

• The maximum value for the range of rows compared by the scan

A scan compares the row with the minimum value, the row with the maximum value, and all of the rows in between the minimum and maximum values in the database object. For each row returned by the query, the value displayed for the minimum value and the maximum value are the values for the column in the displayed the column position. The column position is an index column for the comparison.

To view this information, run the following query:

COLUMN OWNER HEADING 'Comparison|Owner' FORMAT A10
COLUMN COMPARISON_NAME HEADING 'Comparison|Name' FORMAT A22
COLUMN SCAN_ID HEADING 'Scan|ID' FORMAT 9999
COLUMN COLUMN_POSITION HEADING 'Column|Position' FORMAT 999
COLUMN MIN_VALUE HEADING 'Minimum|Value' FORMAT A15
COLUMN MAX_VALUE HEADING 'Maximum|Value' FORMAT A15
 
SELECT OWNER,
       COMPARISON_NAME,
       SCAN_ID,
       COLUMN_POSITION,
       MIN_VALUE,
       MAX_VALUE
  FROM DBA_COMPARISON_SCAN_VALUES
  ORDER BY SCAN_ID;

Your output is similar to the following:

Comparison Comparison              Scan   Column Minimum         Maximum
Owner      Name                      ID Position Value           Value
---------- ---------------------- ----- -------- --------------- ---------------
ADMIN      COMPARE_SUBSET_COLUMNS     1        1 2354            3000
ADMIN      COMPARE_SUBSET_COLUMNS     2        1 2354            2458
ADMIN      COMPARE_SUBSET_COLUMNS     3        1 3000            3000
ADMIN      COMPARE_ORDERS             4        1 2354            3000
ADMIN      COMPARE_ORDERS             5        1 2354            2458
ADMIN      COMPARE_ORDERS             6        1 3000            3000
ADMIN      COMPARE_RANDOM             7        1 2617.3400241505 2940.3400241505
                                                 667163579712423 667163579712423
                                                 44590999096     44590999096
ADMIN      COMPARE_CYCLIC             8        1 2354            2677
ADMIN      COMPARE_CYCLIC             9        1 2354            2458
ADMIN      COMPARE_CUSTOM            10        1 2430            2460
ADMIN      COMPARE_CUSTOM            11        1 2430            2445
ADMIN      COMPARE_CUSTOM            12        1 2446            2458

This output shows the rows that were compared in each scan. For some comparisons, the scan was split into smaller buckets, and the query shows the rows compared in each smaller bucket.

For example, consider the output for the comparison results of the COMPARE_CUSTOM comparison:

• Each scan in the comparison results displays column position 1. To determine which column is in column position 1 for the scan, run the query in "Viewing the Columns Compared by Each Comparison in a Database". In this example, the column in column position 1 for the COMPARE_CUSTOM comparison is the order_id column in the oe.orders table.

• Scan ID 10 is a root scan. This scan found differences, and the rows were split into two buckets that are represented by scan ID 11 and scan ID 12.

• Scan ID 11 compared the rows from the row with 2430 for order_id to the row with 2445 for order_id.

• Scan ID 12 compared the rows from the row with 2446 for order_id to the row with 2458 for order_id.

To recheck or converge the differences found in a scan, you can run the RECHECK function or CONVERGE procedure, respectively. Specify the scan ID of the scan you want to recheck or converge. To recheck or converge all of the rows in comparison results, specify the root scan ID for the comparison results.

The converge_options parameter in the CONVERGE procedure determines which database "wins" during a conversion. To specify that the local database wins, set the converge_options parameter to DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS. When you specify that the local database wins, the data in the database object at the local database replaces the data in the database object at the remote database for each difference found in the specified comparison scan.

To converge a scan of the compare_orders comparison so that both database objects are consistent with the local database, complete the following steps:

The converge_options parameter in the CONVERGE procedure determines which database "wins" during a conversion. To specify that the remote database wins, set the converge_options parameter to DBMS_COMPARISON.CMP_CONVERGE_REMOTE_WINS. When you specify that the remote database wins, the data in the database object at the remote database replaces the data in the database object at the local database for each difference found in the specified comparison scan.

To converge a scan of the compare_orders comparison so that both database objects are consistent with the remote database, complete the following steps:

If the shared database object being converged is part of an Oracle Streams replication environment, then you can set a session tag so that changes made by the CONVERGE procedure are not replicated. Typically, changes made by the CONVERGE procedure should not be replicated to avoid change cycling, which means sending a change back to the database where it originated. In an Oracle Streams replication environment, you can use session tags to ensure that changes made by the CONVERGE procedure are not captured by Oracle Streams capture processes or synchronous captures and therefore not replicated.

To set a session tag in the session running the CONVERGE procedure, use the following procedure parameters:

• The local_converge_tag parameter sets a session tag at the local database. Set this parameter to a value that prevents replication when the remote database wins and the CONVERGE procedure makes changes to the local database.

• The remote_converge_tag parameter sets a session tag at the remote database. Set this parameter to a value that prevents replication when the local database wins and the CONVERGE procedure makes changes to the remote database.

The appropriate value for a session tag depends on the Oracle Streams replication environment. Set the tag to a value that prevents capture processes and synchronous captures from capturing changes made by the session.

The example in this section specifies that the local database wins the converge operation by setting the converge_options parameter to DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS. Therefore, the example sets the remote_converge_tag parameter to the hexadecimal equivalent of '11'. The session tag can be set to any non-NULL value that prevents the changes made by the CONVERGE procedure to the remote database from being replicated.

To converge a scan of the compare_orders comparison so that the database objects are consistent with the local database and a session tag is set at the remote database, complete the following steps:

To purge all of the comparison results for a comparison, specify the comparison name in the comparison_name parameter, and specify the default value of NULL for the scan_id and purge_time parameters.

For example, to purge all of the comparison results for a comparison named compare_orders, log in to SQL*Plus as the owner of the comparison, and run the following procedure:

BEGIN
  DBMS_COMPARISON.PURGE_COMPARISON(
    comparison_name => 'compare_orders',
    scan_id         => NULL,
    purge_time      => NULL);
END;
/

To purge the comparison results for a specific scan of a comparison, specify the comparison name in the comparison_name parameter, and specify the scan ID in the scan_id parameter. The specified scan ID must identify a root scan. The root scan in comparison results is the highest level parent scan. The root scan does not have a parent. You can identify root scan IDs by querying the ROOT_SCAN_ID column of the DBA_COMPARISON_SCAN data dictionary view.

When you run the PURGE_COMPARISON procedure and specify a root scan, the root scan is purged. In addition, all direct and indirect child scans of the specified root scan are purged. Results for other scans are not purged.

For example, to purge the comparison results for scan ID 4 of a comparison named compare_orders, log in to SQL*Plus as the owner of the comparison, and run the following procedure:

BEGIN
  DBMS_COMPARISON.PURGE_COMPARISON(
    comparison_name => 'compare_orders',
    scan_id         => 4); -- Substitute the scan ID from your scan.
END;
/

To purge the comparison results that were recorded on or before a specific date and time for a comparison, specify the comparison name in the comparison_name parameter, and specify the date and time in the purge_time parameter. Results are purged regardless of scan ID. Comparison results that were recorded after the specified date and time are retained.

For example, assume that the NLS_TIMESTAMP_FORMAT initialization parameter setting in the current session is YYYY-MM-DD HH24:MI:SS. To purge the results for any scans that were recorded before 1PM on August 16, 2006 for the compare_orders comparison, log in to SQL*Plus as the owner of the comparison, and run the following procedure:

BEGIN
  DBMS_COMPARISON.PURGE_COMPARISON(
    comparison_name => 'compare_orders',
    purge_time      => '2006-08-16 13:00:00');
END;
/

After an instantiation, you can use the DBMS_COMPARISON package to verify the consistency of the database objects that were instantiated. Typically, you should verify consistency before the Oracle Streams replication environment is replicating changes. Ensure that you check for consistency before you allow changes to the source database object and the instantiated database object. Changes to these database objects are identified as differences by the DBMS_COMPARISON package.

To verify the consistency of instantiated database objects, complete the following steps:

Oracle Streams replication environments continually replicate changes to database objects. Therefore, the following applies to the replicated database objects:

• Replicated database objects should be nearly synchronized most of the time because Oracle Streams components replicate and apply changes to keep them synchronized.

• If there are differences in replicated database objects, then Oracle Streams components will typically send and apply changes to synchronize the database objects in the near future. That is, a COMPARE function might show differences that are in the process of being replicated.

Because differences are expected in database objects while changes are being replicated, using the DBMS_COMPARISON package to compare replicated database objects can be challenging. For example, assume that there is an existing comparison that compares an entire table at two databases, and consider the following scenario:

When differences are found, and you suspect that the differences are transient, you can run the RECHECK function after some time has passed. If Oracle Streams has synchronized the database objects, then the differences will disappear.

If some rows in a replicated database object are constantly updated, then these rows might always show differences in comparison results. In this case, as you monitor the environment, ensure the following:

• No apply errors are accumulating at the destination database for these rows.

• The rows are being updated correctly by the Oracle Streams apply process at the destination database. You can query the table that contains the rows at the destination database to ensure that the replicated changes are being applied.

When both of these statements are true for the rows, then you can ignore differences in the comparison results for them.

Because the COMPARE function might show differences that are in the process of being replicated, it is best to run this function during times when there is the least amount of replication activity in your environment. During times of relatively little replication activity, comparison results show the following types of differences in an Oracle Streams replication environment:

• Differences resulting when rows are manually manipulated at only one database by an administrator or procedure. For example, an administrator or procedure might set a session tag before making changes, and the session tag might prevent a capture process from capturing the changes.

• Differences resulting from recovery situations in which data is lost at one database and must be identified and recovered from another database.

• Differences resulting from apply errors. In this case, the error transactions are not applied at one database because of apply errors.

In any of these situations, you can run the CONVERGE procedure to synchronize the database objects if it is appropriate. For example, if there are apply errors, and it is not easy to reexecute the error transactions, then you can use the CONVERGE procedure to synchronize the database objects.