Tutorial: Preparing to Compare and Converge Data

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 Configuring the Network Environment for information about configuring network connectivity between databases.

• Ensure that the hr sample schema is installed on both databases. The hr sample schema is installed by default with Oracle Database.

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 Starting SQL*Plus and Connecting to the Database 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 "Tutorial: Creating a Database Link" for instructions.