Your browser does not support JavaScript. This help page requires JavaScript to render correctly.
Skip Headers
Previous
Previous
 
Next
Next

About Comparing and Converging Data in Different Databases

You can share database objects at two or more databases. When copies of the same database object exist at multiple databases, the database object is a shared database object. Shared database objects might be maintained by data replication. For example, materialized views or Oracle Streams components might replicate the database objects and maintain them at multiple databases. A custom application might also maintain shared database objects. Typically, replication environments share database objects that contain data, such as tables, as well as other types of databases objects, such as indexes.

When a change is made to a shared database object at one database, the change is transferred to and made at each of the other databases that share the database object. In this way, the replication environment keeps the shared database object synchronized at each database.

Sometimes, shared database objects that contain data can become inconsistent at different databases. That is, the data might diverge in the different instances of the shared database object. For example, if the database object is a table, then one instance of the table might have more rows than another instance of the table, or two instances of the table might have different data in the same rows.

When shared database objects diverge in an Oracle Streams replication environment, it is usually for one of the following reasons:

Common causes of data divergence are network problems, incorrect configurations, or user errors. When shared database objects diverge in a replication environment that uses materialized views, it might be because there is a problem with the materialized view refresh.

The DBMS_COMPARISON package enables you to compare database objects at different databases and identify differences. This package also enables you to converge the database objects so that they are consistent at different databases. The DBMS_COMPARISON package is an Oracle-supplied PL/SQL package that is always installed with Oracle Database.

The DBMS_COMPARISON package can compare and converge the following types of database objects:

Database objects of different types can be compared and converged at different databases. For example, a table at one database and a materialized view at another database can be compared and converged.

In the examples in this guide, the shared database object has the same name at the two databases, and the entire database object is compared and converged. However, the DBMS_COMPARISON package provides flexibility for differences in the shared database object at different databases. The database objects being compared do not need to have the same name. In addition, column names can also be different in the database objects, as long as the corresponding columns are the same data type. You can compare and converge the entire shared database object or subsets of columns and rows.

To create a comparison, use the CREATE_COMPARISON procedure in the DBMS_COMPARISON package. This procedure identifies one or more index columns in the shared database object. The DBMS_COMPARISON package must be able to identify at least one column that it can use as an index column. If the specified database object does not have a column that can be used as an index column, then the CREATE_COMPARISON procedure cannot create a comparison for the database object.

Note: If your environment has shared database objects that have diverged, then you should investigate the cause of the problem and correct it. Although the DBMS_COMPARISON package can compare and converge shared database objects, it is better if these database objects do not diverge.

Related Topics

Oracle Database PL/SQL Packages and Types Reference for detailed information about the DBMS_COMPARISON package

Oracle Streams Replication Administrator's Guide for information about using the advanced features of the DBMS_COMPARISON package

Tutorial: Preparing to Compare and Converge Data

Tutorial: Comparing Data in Two Different Databases

Tutorial: Converging Divergent Data