Previous |
Next |
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:
Data changes are not being captured at one or more of the databases.
Data changes are being captured, but they are not being transferred from one database to another.
Data changes are being captured and transferred from one database to another, but they are not being made to shared database objects at the other databases.
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:
Tables
Single-table views
Materialized views
Synonyms for tables, single-table views, and materialized views
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.
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