35 DBMS_COMPARISON

Using DBMS_COMPARISON

This section contains topics which relate to using the DBMS_COMPARISON package.

• Overview

• Security Model

• Constants

• Views

• Operational Notes

Overview

The DBMS_COMPARISON package is an Oracle-supplied package that you can use to compare database objects at two databases. This package also enables you converge the database objects so that they are consistent at different databases. Typically, this package is used in environments that share a database object at multiple databases. When copies of the same database object exist at multiple databases, the database object is a shared database object. Several data dictionary views contain information about comparisons made with the DBMS_COMPARISON package.

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. When a database object is shared, it can diverge at the databases that share it. You can use this package to identify differences in the shared database objects. After identifying the differences, you can optionally use this package to synchronize the shared database objects.

To compare a database object that is shared at two different databases, complete the following general steps:

1. Run the CREATE_COMPARE procedure in this package to create a comparison. The comparison identifies the database objects to compare and specifies parameters for the comparison.

2. Run the COMPARE function in this package to compare the database object at the two databases and identify differences. This function returns TRUE when no differences are found and FALSE when differences are found. This function also populates data dictionary views with comparison results. Separate comparison results are generated for each execution of the COMPARE function.

3. If you want to examine the comparison results, query the following data dictionary views:

   • DBA_COMPARISON_SCAN

   • USER_COMPARISON_SCAN

   • DBA_COMPARISON_SCAN_VALUES

   • USER_COMPARISON_SCAN_VALUES

   • DBA_COMPARISON_ROW_DIF

   • USER_COMPARISON_ROW_DIF

4. If there are differences, and you want to synchronize the database objects at the two databases, then run the CONVERGE procedure in this package.

After you create a comparison with the CREATE_COMPARISON procedure in the DBMS_COMPARISON package, you can run the comparison at any time using the COMPARE function. Each time you run the COMPARE function, it records comparison results in the appropriate data dictionary views. Comparison results might be modified when subprograms in this package are invoked and the scans in the comparison results are specified. For example, comparison results might be modified when you run the RECHECK function.

The comparison results for a single execution of the COMPARE function can include one or more scans. A scan checks for differences in some or all of the rows in a shared database object at a single point in time. You can compare database objects multiple times, and a unique scan ID identifies each scan in the comparison results.

A bucket is a range of rows in a database object that is being compared. Buckets improve performance by splitting the database object into ranges and comparing the ranges independently. Every comparison divides the rows being compared into an appropriate number of buckets, and each bucket is compared by a scan.

Each time the COMPARE function splits a bucket into smaller buckets, it performs new scans of the smaller buckets. The scan that analyzes a larger bucket is the parent scan of each scan that analyzes the smaller buckets into which the larger bucket was split. The root scan in the comparison results is the highest level parent scan. The root scan does not have a parent.

You can recheck a scan using the RECHECK function, and you can converge a scan using the CONVERGE procedure. When you want to recheck or converge all of the rows comparison results, specify the root scan ID for the comparison results in the appropriate subprogram. When you want to recheck or converge a portion of the rows in comparison results, specify the scan ID of the scan that contains the differences.

Security Model

Security on this package can be controlled in either of the following ways:

• Granting EXECUTE on this package to selected users or roles.

• Granting EXECUTE_CATALOG_ROLE to selected users or roles.

If subprograms in the package are run from within a stored procedure, then the user who runs the subprograms must be granted EXECUTE privilege on the package directly. It cannot be granted through a role.

Each subprogram in the DBMS_COMPARISON package has a comparison_name parameter. The current user must be the owner of the specified comparison to run a subprogram in the DBMS_COMPARISON package.

To run the COMPARE function, RECHECK function, or CONVERGE procedure, the following users must have SELECT privilege on each copy of the shared database object:

• The comparison owner at the local database

• When a database link is used, the user at the remote database to which the comparison owner connects through a database link

The CONVERGE procedure also requires additional privileges for one of these users at the database where it makes changes to the shared database object. The user must have INSERT, UPDATE, and DELETE privileges on the shared database object at this database.

In addition, when the CONVERGE procedure is run with either the local_converge_tag or remote_converge_tag parameter set to a non-NULL value, then the following additional requirements must be met:

• If the local table "wins," then the user at the remote database to which the invoker of the CONVERGE procedure connects through a database link must be granted either EXECUTE_CATALOG_ROLE or EXECUTE privilege on the DBMS_STREAMS_ADM package.

• If the remote table "wins," then the invoker of the CONVERGE procedure at the local database must be granted either EXECUTE_CATALOG_ROLE or EXECUTE privilege on the DBMS_STREAMS_ADM package.

Constants

The DBMS_COMPARISON package defines several enumerated constants to use specifying parameter values. Enumerated constants must be prefixed with the package name. For example, DBMS_COMPARISON.CMP_SCAN_MODE_FULL.

Table 35-1 lists the parameters and enumerated constants.

Views

The DBMS_COMPARISON package uses the following views:

• DBA_COMPARISON

• USER_COMPARISON

• DBA_COMPARISON_COLUMNS

• USER_COMPARISON_COLUMNS

• DBA_COMPARISON_SCAN

• USER_COMPARISON_SCAN

• DBA_COMPARISON_SCAN_VALUES

• USER_COMPARISON_SCAN_VALUES

• DBA_COMPARISON_ROW_DIF

• USER_COMPARISON_ROW_DIF

Operational Notes

This section contains the following operational notes for the DBMS_COMPARISON package:

• Oracle Database Release Requirements for the DBMS_COMPARISON Package

• Database Character Set Requirements for the DBMS_COMPARISON Package

• Database Object Requirements for the DBMS_COMPARISON Package

• Index Column Requirements for the DBMS_COMPARISON Package

• Data Type Requirements for the DBMS_COMPARISON Package

• Only Converge Rows That Are Not Being Updated

Oracle Database Release Requirements for the DBMS_COMPARISON Package

Meet the following Oracle Database release requirements when running the subprograms in the DBMS_COMPARISON package:

• The local database that runs the subprograms in the DBMS_COMPARISON package must be an Oracle Database 11g Release 1 (11.1) database.

• The remote database must be an Oracle Database 10g Release 1 (10.1) or later database. Oracle databases before this release and non-Oracle databases are not supported.

Database Character Set Requirements for the DBMS_COMPARISON Package

The database character sets must be the same for the databases that contain the database objects being compared.

Database Object Requirements for the DBMS_COMPARISON Package

The DBMS_COMPARISON package can compare 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 with this package.

To run the subprograms in the DBMS_COMPARISON package, the specified database objects must have the same shape at each database. Specifically, the database objects must have the same number of columns at each database, and the data types of corresponding columns must match.

If a database object being compared contains columns that do not exist in the other database object, then you can compare the database objects by excluding the extra columns during comparison creation. Use the column_list parameter in the CREATE_COMPARISON procedure to list only the columns that exist in both database objects.

Index Column Requirements for the DBMS_COMPARISON Package

This section discusses number, timestamp, and interval columns. These include the following data types:

• Number columns are of the following data types: NUMBER, FLOAT, BINARY_FLOAT, and BINARY_DOUBLE.

• Timestamp columns are of the following data types: TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE

• Interval columns are of the following data types: INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND.

For all scan modes to be supported by the DBMS_COMPARISON package, the database objects must have one of the following types of indexes:

• A single-column index on a number, timestamp, interval, or DATE data type column

• A composite index that only includes number, timestamp, interval, or DATE data type columns. Each column in the composite index must either have a NOT NULL constraint or be part of the primary key.

For the scan modes CMP_SCAN_MODE_FULL and CMP_SCAN_MODE_CUSTOM to be supported, the database objects must have one of the following types of indexes:

• A single-column index on a number, timestamp, interval, DATE, VARCHAR2, or CHAR data type column

• A composite index that only includes number, timestamp, interval, DATE, VARCHAR2, or CHAR columns. Each column in the composite index must either have a NOT NULL constraint or be part of the primary key.

If the database objects do not have one of these types of indexes, then the DBMS_COMPARISON package does not support the database objects. For example, if the database objects only have a single index on an NVARCHAR2 column, then the DBMS_COMPARISON package does not support them. Or, if the database objects have only one index, and it is a composite index that includes a NUMBER column and an NCHAR column, then the DBMS_COMPARISON package does not support them.

You can specify an index when you create a comparison using the index_schema_name and index_name parameters in the CREATE_COMPARISON procedure. If you specify an index, then make sure the columns in the index meet the requirements of the scan mode used for the comparison.

The index columns in a comparison must uniquely identify every row involved in a comparison. The following constraints satisfy this requirement:

• A primary key constraint

• A unique constraint on one or more non-NULL columns

If these constraints are not present on a table, then use the index_schema_name and index_name parameters in the CREATE_COMPARISON procedure to specify an index whose columns satisfy this requirement.

When a single index value identifies both a local row and a remote row, the two rows must be copies of the same row in the replicated tables. In addition, each pair of copies of the same row must always have the same index value.

The DBMS_COMPARISON package can use an index only if all of the columns in the index are included in the column_list parameter when the comparison is created with the CREATE_COMPARISON procedure.

After a comparison is created, you can determine the index column or columns for it by running the following query:

SELECT COLUMN_NAME, COLUMN_POSITION FROM DBA_COMPARISON_COLUMNS 
  WHERE COMPARISON_NAME = 'COMPARE_CUSTOM' AND
        INDEX_COLUMN    = 'Y';

If there are multiple index columns, then the index column with 1 for the COLUMN_POSITION is the lead index column in the composite index.

Data Type Requirements for the DBMS_COMPARISON Package

The DBMS_COMPARISON package can compare data in columns of the following data types:

• VARCHAR2

• NVARCHAR2

• NUMBER

• FLOAT

• DATE

• BINARY_FLOAT

• BINARY_DOUBLE

• TIMESTAMP

• TIMESTAMP WITH TIME ZONE

• TIMESTAMP WITH LOCAL TIME ZONE

• INTERVAL YEAR TO MONTH

• INTERVAL DAY TO SECOND

• RAW

• CHAR

• NCHAR

If a column with data type TIMESTAMP WITH LOCAL TIME ZONE is compared, then the two databases must use the same time zone. Also, if a column with data type NVARCHAR2 or NCHAR is compared, then the two databases must use the same national character set.

The DBMS_COMPARISON package cannot compare data in columns of the following data types:

• LONG

• LONG RAW

• ROWID

• UROWID

• CLOB

• NCLOB

• BLOB

• BFILE

• User-defined types (including object types, REFs, varrays, and nested tables)

• Oracle-supplied types (including any types, XML types, spatial types, and media types)

You can compare database objects that contain unsupported columns by excluding the unsupported columns during comparison creation. Use the column_list parameter in the CREATE_COMPARISON procedure to list only the supported columns in a shared database object.

Only Converge Rows That Are Not Being Updated

You should only converge rows that are not being updated on either database. For example, if the shared database object is updated by replication components, then only converge rows for which replication changes have been applied and make sure no new changes are in the process of being replicated for these rows. If you compare replicated database objects, then it is typically best to compare them during a time of little or no replication activity to identify persistent differences.

Data Structures

The DBMS_COMPARISON package defines a RECORD type.

RECORD Types

• COMPARISON_TYPE Record Type

COMPARISON_TYPE Record Type

Contains information returned by the COMPARE function or CONVERGE procedure in the DBMS_COMPARISON package.

Syntax

TYPE COMPARISON_TYPE IS RECORD(
  scan_id            NUMBER,
  loc_rows_merged    NUMBER,
  rmt_rows_merged    NUMBER,
  loc_rows_deleted   NUMBER,
  rmt_rows_deleted   NUMBER);

Fields

Summary of DBMS_COMPARISON Subprograms

COMPARE Function

This function performs the specified comparison.

Each time a comparison is performed, it results in at least one new scan, and each scan has a unique scan ID. You can define and name a comparison using the CREATE_COMPARISON procedure.

Syntax

DBMS_COMPARISON.COMPARE(
   comparison_name  IN   VARCHAR2,
   scan_info        OUT  COMPARISON_TYPE,
   min_value        IN   VARCHAR2   DEFAULT NULL,
   max_value        IN   VARCHAR2   DEFAULT NULL,
   perform_row_dif  IN   BOOLEAN    DEFAULT FALSE)
RETURN BOOLEAN;

Parameters

Return Values

This function returns TRUE when no differences are found in the database objects being compared. This function returns FALSE when differences are found in the database objects being compared.

CONVERGE Procedure

This procedure executes data manipulation language (DML) changes to synchronize the portion of the database objects that was compared in the specified scan.

Syntax

DBMS_COMPARISON.CONVERGE(
   comparison_name      IN   VARCHAR2,
   scan_id              IN   NUMBER,
   scan_info            OUT  COMPARISON_TYPE,
   converge_options     IN   VARCHAR2  DEFAULT CMP_CONVERGE_LOCAL_WINS,
   perform_commit       IN   BOOLEAN   DEFAULT TRUE,
   local_converge_tag   IN   RAW       DEFAULT NULL,
   remote_converge_tag  IN   RAW       DEFAULT NULL);

Parameters

Usage Notes

If one of the database objects being converged is a read-only materialized view, then the converge_options parameter must be set to ensure that the read-only materialized view "wins" in the converge operation. The CONVERGE procedure raises an error if it tries to make changes to a read-only materialized view.

CREATE_COMPARISON Procedure

This procedure creates a comparison.

Syntax

DBMS_COMPARISON.CREATE_COMPARISON(
   comparison_name      IN  VARCHAR2,
   schema_name          IN  VARCHAR2,
   object_name          IN  VARCHAR2,
   dblink_name          IN  VARCHAR2,
   index_schema_name    IN  VARCHAR2  DEFAULT NULL,
   index_name           IN  VARCHAR2  DEFAULT NULL,
   remote_schema_name   IN  VARCHAR2  DEFAULT NULL,
   remote_object_name   IN  VARCHAR2  DEFAULT NULL,
   comparison_mode      IN  VARCHAR2  DEFAULT CMP_COMPARE_MODE_OBJECT,
   column_list          IN  VARCHAR2  DEFAULT '*',
   scan_mode            IN  VARCHAR2  DEFAULT CMP_SCAN_MODE_FULL,
   scan_percent         IN  NUMBER    DEFAULT NULL,
   null_value           IN  VARCHAR2  DEFAULT CMP_NULL_VALUE_DEF,
   local_converge_tag   IN  RAW       DEFAULT NULL,
   remote_converge_tag  IN  RAW       DEFAULT NULL,
   max_num_buckets      IN  NUMBER    DEFAULT CMP_MAX_NUM_BUCKETS,
   min_rows_in_bucket   IN  NUMBER    DEFAULT CMP_MIN_ROWS_IN_BUCKET);

Parameters

Usage Notes

This section contains usage notes for the CREATE_COMPARISON procedure.

Usage Notes for the index_schema_name and index_name Parameters

When you specify an index for a comparison with the index_schema_name and index_name parameters, the specified index determines the comparison's index columns and their ordering. The order of the columns in the index determines the index column ordering for the comparison. Therefore, the column in column position 1 in the index is the lead column for the comparison.

The index columns and their ordering affect the details of each SQL statement generated and executed for a comparison. For each SQL statement, the optimizer decides whether to use indexes. If the optimizer decides to use indexes, then the optimizer decides which particular indexes to use. An index specified in column_list parameter might or might not be used.

The columns in the specified index must meet the requirements described in "Index Column Requirements for the DBMS_COMPARISON Package". If the index columns do not meet these requirements, then an error is raised.

Usage Notes for the column_list Parameter

When the column_list parameter is set to a value other than '*', the following columns are required in the column_list parameter:

• Any columns that are required to meet the index column requirements for the DBMS_COMPARISON package. If the index_name parameter is non-NULL, then the columns in the specified index must be in the column list. If the index_name parameter is NULL, then see "Index Column Requirements for the DBMS_COMPARISON Package".

• If you plan to use the CONVERGE procedure to make changes to a database object based on the comparison, then any columns in this database object that have a NOT NULL constraint but no default value must be included in the column list. If these columns are not included, then the CONVERGE procedure returns an error. See CONVERGE Procedure.

DROP_COMPARISON Procedure

This procedure drops a comparison.

Syntax

DBMS_COMPARISON.DROP_COMPARISON(
   comparison_name  IN  VARCHAR2);

Parameters

PURGE_COMPARISON Procedure

This procedure purges the comparison results, or a subset of the comparison results, for a comparison.

Syntax

DBMS_COMPARISON.PURGE_COMPARISON(
   comparison_name  IN  VARCHAR2,
   scan_id          IN  NUMBER     DEFAULT NULL,
   purge_time       IN  TIMESTAMP  DEFAULT NULL);

Parameters

RECHECK Function

This function rechecks the differences in a specified scan for a comparison.

This function performs one of the following actions:

• If the specified scan completed successfully the last time it ran, then this function checks the previously identified differences in the scan.

• If the specified scan completed partially, then this function continues to check the database object from the point where the previous scan ended.

Syntax

DBMS_COMPARISON.RECHECK(
   comparison_name  IN  VARCHAR2,
   scan_id          IN  NUMBER,
   perform_row_dif  IN  BOOLEAN  DEFAULT FALSE)
RETURN BOOLEAN;

Parameters

Return Values

This function returns TRUE when no differences are found in the database objects being compared. This function returns FALSE when differences are found in the database objects being compared.