9 Oracle Streams Conflict Resolution

Some Oracle Streams environments must use conflict handlers to resolve possible data conflicts that can result from sharing data between multiple databases.

This chapter contains these topics:

• About DML Conflicts in an Oracle Streams Environment

• Conflict Types in an Oracle Streams Environment

• Conflicts and Transaction Ordering in an Oracle Streams Environment

• Conflict Detection in an Oracle Streams Environment

• Conflict Avoidance in an Oracle Streams Environment

• Conflict Resolution in an Oracle Streams Environment

• Managing Oracle Streams Conflict Detection and Resolution

• Monitoring Conflict Detection and Update Conflict Handlers

9.1 About DML Conflicts in an Oracle Streams Environment

A conflict is a mismatch between the old values in an LCR and the expected data in a table. Conflicts can occur in an Oracle Streams environment that permits concurrent data manipulation language (DML) operations on the same data at multiple databases. In an Oracle Streams environment, DML conflicts can occur only when an apply process is applying a message that contains a row change resulting from a DML operation. This type of message is called a row logical change record, or row LCR. An apply process automatically detects conflicts caused by row LCRs.

For example, when two transactions originating at different databases update the same row at nearly the same time, a conflict can occur. When you configure an Oracle Streams environment, you must consider whether conflicts can occur. You can configure conflict resolution to resolve conflicts automatically, if your system design permits conflicts.

In general, you should try to design an Oracle Streams environment that avoids the possibility of conflicts. Using the conflict avoidance techniques discussed later in this chapter, most system designs can avoid conflicts in all or a large percentage of the shared data. However, many applications require that some percentage of the shared data be updatable at multiple databases at any time. If this is the case, then you must address the possibility of conflicts.

Note:

An apply process does not detect DDL conflicts or conflicts resulting from user messages. Ensure that your environment avoids these types of conflicts.

See Also:

Oracle Streams Concepts and Administration for more information about row LCRs

9.2 Conflict Types in an Oracle Streams Environment

You can encounter these types of conflicts when you share data at multiple databases:

• Update Conflicts in an Oracle Streams Environment

• Uniqueness Conflicts in an Oracle Streams Environment

• Delete Conflicts in an Oracle Streams Environment

• Foreign Key Conflicts in an Oracle Streams Environment

9.2.1 Update Conflicts in an Oracle Streams Environment

An update conflict occurs when the apply process applies a row LCR containing an update to a row that conflicts with another update to the same row. Update conflicts can happen when two transactions originating from different databases update the same row at nearly the same time.

9.2.2 Uniqueness Conflicts in an Oracle Streams Environment

A uniqueness conflict occurs when the apply process applies a row LCR containing a change to a row that violates a uniqueness integrity constraint, such as a PRIMARY KEY or UNIQUE constraint. For example, consider what happens when two transactions originate from two different databases, each inserting a row into a table with the same primary key value. In this case, the transactions cause a uniqueness conflict.

9.2.3 Delete Conflicts in an Oracle Streams Environment

A delete conflict occurs when two transactions originate at different databases, with one transaction deleting a row and another transaction updating or deleting the same row. In this case, the row referenced in the row LCR does not exist to be either updated or deleted.

9.2.4 Foreign Key Conflicts in an Oracle Streams Environment

A foreign key conflict occurs when the apply process applies a row LCR containing a change to a row that violates a foreign key constraint. For example, in the hr schema, the department_id column in the employees table is a foreign key of the department_id column in the departments table. Consider what can happen when the following changes originate at two different databases (A and B) and are propagated to a third database (C):

• At database A, a row is inserted into the departments table with a department_id of 271. This change is propagated to database B and applied there.

• At database B, a row is inserted into the employees table with an employee_id of 206 and a department_id of 271.

If the change that originated at database B is applied at database C before the change that originated at database A, then a foreign key conflict results because the row for the department with a department_id of 271 does not yet exist in the departments table at database C.

9.3 Conflicts and Transaction Ordering in an Oracle Streams Environment

Ordering conflicts can occur in an Oracle Streams environment when three or more databases share data and the data is updated at two or more of these databases. For example, consider a scenario in which three databases share information in the hr.departments table. The database names are mult1.example.com, mult2.example.com, and mult3.example.com. Suppose a change is made to a row in the hr.departments table at mult1.example.com that will be propagated to both mult2.example.com and mult3.example.com. The following series of actions might occur:

1. The change is propagated to mult2.example.com.

2. An apply process at mult2.example.com applies the change from mult1.example.com.

3. A different change to the same row is made at mult2.example.com.

4. The change at mult2.example.com is propagated to mult3.example.com.

5. An apply process at mult3.example.com attempts to apply the change from mult2.example.com before another apply process at mult3.example.com applies the change from mult1.example.com.

In this case, a conflict occurs because a column value for the row at mult3.example.com does not match the corresponding old value in the row LCR propagated from mult2.example.com.

In addition to causing a data conflict, transactions that are applied out of order might experience referential integrity problems at a remote database if supporting data has not been successfully propagated to that database. Consider the scenario where a new customer calls an order department. A customer record is created and an order is placed. If the order data is applied at a remote database before the customer data, then a referential integrity error is raised because the customer that the order references does not exist at the remote database.

If an ordering conflict is encountered, then you can resolve the conflict by reexecuting the transaction in the error queue after the required data has been propagated to the remote database and applied.

9.4 Conflict Detection in an Oracle Streams Environment

An apply process detects update, uniqueness, delete, and foreign key conflicts as follows:

• An apply process detects an update conflict if there is any difference between the old values for a row in a row LCR and the current values of the same row at the destination database.

• An apply process detects a uniqueness conflict if a uniqueness constraint violation occurs when applying an LCR that contains an insert or update operation.

• An apply process detects a delete conflict if it cannot find a row when applying an LCR that contains an update or delete operation, because the primary key of the row does not exist.

• An apply process detects a foreign key conflict if a foreign key constraint violation occurs when applying an LCR.

A conflict can be detected when an apply process attempts to apply an LCR directly or when an apply process handler, such as a DML handler, runs the EXECUTE member procedure for an LCR. A conflict can also be detected when either the EXECUTE_ERROR or EXECUTE_ALL_ERRORS procedure in the DBMS_APPLY_ADM package is run.

Note:

• If a column is updated and the column's old value equals its new value, then Oracle never detects a conflict for this column update.

• Any old LOB values in update LCRs, delete LCRs, and LCRs dealing with piecewise updates to LOB columns are not used by conflict detection.

9.4.1 Control Over Conflict Detection for Nonkey Columns

By default, an apply process compares old values for all columns during conflict detection, but you can stop conflict detection for nonkey columns using the COMPARE_OLD_VALUES procedure in the DBMS_APPLY_ADM package. Conflict detection might not be needed for some nonkey columns.

See Also:

• "Stopping Conflict Detection for Nonkey Columns"

• "Displaying Information About Conflict Detection"

9.4.2 Rows Identification During Conflict Detection in an Oracle Streams Environment

To detect conflicts accurately, Oracle must be able to identify and match corresponding rows at different databases uniquely. By default, Oracle uses the primary key of a table to identify rows in a table uniquely. When a table does not have a primary key, you should designate a substitute key. A substitute key is a column or set of columns that Oracle can use to identify uniquely rows in the table.

See Also:

Oracle Streams Concepts and Administration

9.5 Conflict Avoidance in an Oracle Streams Environment

The following topics describe ways to avoid data conflicts:

• Use a Primary Database Ownership Model

• Avoid Specific Types of Conflicts

9.5.1 Use a Primary Database Ownership Model

You can avoid the possibility of conflicts by limiting the number of databases in the system that have simultaneous update access to the tables containing shared data. Primary ownership prevents all conflicts, because only a single database permits updates to a set of shared data. Applications can even use row and column subsetting to establish more granular ownership of data than at the table level. For example, applications might have update access to specific columns or rows in a shared table on a database-by-database basis.

9.5.2 Avoid Specific Types of Conflicts

If a primary database ownership model is too restrictive for your application requirements, then you can use a shared ownership data model, which means that conflicts might be possible. Even so, typically you can use some simple strategies to avoid specific types of conflicts.

The following topics describe strategies for avoiding specific types of conflicts:

• Avoid Uniqueness Conflicts in an Oracle Streams Environment

• Avoid Delete Conflicts in an Oracle Streams Environment

• Avoid Update Conflicts in an Oracle Streams Environment

9.5.2.1 Avoid Uniqueness Conflicts in an Oracle Streams Environment

You can avoid uniqueness conflicts by ensuring that each database uses unique identifiers for shared data. There are three ways to ensure unique identifiers at all databases in an Oracle Streams environment.

One way is to construct a unique identifier by executing the following select statement:

SELECT SYS_GUID() OID FROM DUAL;

This SQL operator returns a 16-byte globally unique identifier. This value is based on an algorithm that uses time, date, and the computer identifier to generate a globally unique identifier. The globally unique identifier appears in a format similar to the following:

A741C791252B3EA0E034080020AE3E0A

Another way to avoid uniqueness conflicts is to create a sequence at each of the databases that shares data and concatenate the database name (or other globally unique value) with the local sequence. This approach helps to avoid any duplicate sequence values and helps to prevent uniqueness conflicts.

Finally, you can create a customized sequence at each of the databases that shares data so that no two databases can generate the same value. You can accomplish this by using a combination of starting, incrementing, and maximum values in the CREATE SEQUENCE statement. For example, you might configure the following sequences:

Table 9-1 Customized Sequences for Oracle Streams Replication Environments

Parameter	Database A	Database B	Database C
START WITH	1	3	5
INCREMENT BY	10	10	10
Range Example	1, 11, 21, 31, 41,...	3, 13, 23, 33, 43,...	5, 15, 25, 35, 45,...

Using a similar approach, you can define different ranges for each database by specifying a START WITH and MAXVALUE that would produce a unique range for each database.

9.5.2.2 Avoid Delete Conflicts in an Oracle Streams Environment

Always avoid delete conflicts in shared data environments. In general, applications that operate within a shared ownership data model should not delete rows using DELETE statements. Instead, applications should mark rows for deletion and then configure the system to purge logically deleted rows periodically.

9.5.2.3 Avoid Update Conflicts in an Oracle Streams Environment

After trying to eliminate the possibility of uniqueness and delete conflicts, you should also try to limit the number of possible update conflicts. However, in a shared ownership data model, update conflicts cannot be avoided in all cases. If you cannot avoid all update conflicts, then you must understand the types of conflicts possible and configure the system to resolve them if they occur.

9.6 Conflict Resolution in an Oracle Streams Environment

After an update conflict has been detected, a conflict handler can attempt to resolve it. Oracle Streams provides prebuilt conflict handlers to resolve update conflicts, but not uniqueness, delete, foreign key, or ordering conflicts. However, you can build your own custom conflict handler to resolve data conflicts specific to your business rules. Such a conflict handler can be part of a procedure DML handler or an error handler.

Whether you use prebuilt or custom conflict handlers, a conflict handler is applied as soon as a conflict is detected. If neither the specified conflict handler nor the relevant apply handler can resolve the conflict, then the conflict is logged in the error queue. You might want to use the relevant apply handler to notify the database administrator when a conflict occurs.

When a conflict causes a transaction to be moved to the error queue, sometimes it is possible to correct the condition that caused the conflict. In these cases, you can reexecute a transaction using the EXECUTE_ERROR procedure in the DBMS_APPLY_ADM package.

See Also:

• Oracle Streams Concepts and Administration for more information about procedure DML handlers, error handlers, and the error queue

• Oracle Database PL/SQL Packages and Types Reference for more information about the EXECUTE_ERROR procedure in the DBMS_APPLY_ADM package

9.6.1 Prebuilt Update Conflict Handlers

This section describes the types of prebuilt update conflict handlers available to you and how column lists and resolution columns are used in prebuilt update conflict handlers. A column list is a list of columns for which the update conflict handler is called when there is an update conflict. The resolution column is the column used to identify an update conflict handler. If you use a MAXIMUM or MINIMUM prebuilt update conflict handler, then the resolution column is also the column used to resolve the conflict. The resolution column must be one of the columns in the column list for the handler.

Use the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package to specify one or more update conflict handlers for a particular table. There are no prebuilt conflict handlers for uniqueness, delete, or foreign key conflicts.

See Also:

• "Managing Oracle Streams Conflict Detection and Resolution" for instructions on adding, modifying, and removing an update conflict handler

• Oracle Database PL/SQL Packages and Types Reference for more information about the SET_UPDATE_CONFLICT_HANDLER procedure

• "Column Lists"

• "Resolution Columns"

9.6.1.1 Types of Prebuilt Update Conflict Handlers

Oracle provides the following types of prebuilt update conflict handlers for an Oracle Streams environment: OVERWRITE, DISCARD, MAXIMUM, and MINIMUM.

The description for each type of handler later in this section refers to the following conflict scenario:

1. The following update is made at the dbs1.example.com source database:

   UPDATE hr.employees SET salary = 4900 WHERE employee_id = 200;
   COMMIT;
   

   This update changes the salary for employee 200 from 4400 to 4900.

2. At nearly the same time, the following update is made at the dbs2.example.com destination database:

   UPDATE hr.employees SET salary = 5000 WHERE employee_id = 200;
   COMMIT;
   

3. A capture process or synchronous capture captures the update at the dbs1.example.com source database and puts the resulting row LCR in a queue.

4. A propagation propagates the row LCR from the queue at dbs1.example.com to a queue at dbs2.example.com.

5. An apply process at dbs2.example.com attempts to apply the row LCR to the hr.employees table but encounters a conflict because the salary value at dbs2.example.com is 5000, which does not match the old value for the salary in the row LCR (4400).

The following sections describe each prebuilt conflict handler and explain how the handler resolves this conflict.

9.6.1.1.1 OVERWRITE

When a conflict occurs, the OVERWRITE handler replaces the current value at the destination database with the new value in the LCR from the source database.

If the OVERWRITE handler is used for the hr.employees table at the dbs2.example.com destination database in the conflict example, then the new value in the row LCR overwrites the value at dbs2.example.com. Therefore, after the conflict is resolved, the salary for employee 200 is 4900.

9.6.1.1.2 DISCARD

When a conflict occurs, the DISCARD handler ignores the values in the LCR from the source database and retains the value at the destination database.

If the DISCARD handler is used for the hr.employees table at the dbs2.example.com destination database in the conflict example, then the new value in the row LCR is discarded. Therefore, after the conflict is resolved, the salary for employee 200 is 5000 at dbs2.example.com.

9.6.1.1.3 MAXIMUM

When a conflict occurs, the MAXIMUM conflict handler compares the new value in the LCR from the source database with the current value in the destination database for a designated resolution column. If the new value of the resolution column in the LCR is greater than the current value of the column at the destination database, then the apply process resolves the conflict in favor of the LCR. If the new value of the resolution column in the LCR is less than the current value of the column at the destination database, then the apply process resolves the conflict in favor of the destination database.

If the MAXIMUM handler is used for the salary column in the hr.employees table at the dbs2.example.com destination database in the conflict example, then the apply process does not apply the row LCR, because the salary in the row LCR is less than the current salary in the table. Therefore, after the conflict is resolved, the salary for employee 200 is 5000 at dbs2.example.com.

If you want to resolve conflicts based on the time of the transactions involved, then one way to do this is to add a column to a shared table that automatically records the transaction time with a trigger. You can designate this column as a resolution column for a MAXIMUM conflict handler, and the transaction with the latest (or greater) time would be used automatically.

The following is an example of a trigger that records the time of a transaction for the hr.employees table. Assume that the job_id, salary, and commission_pct columns are part of the column list for the conflict resolution handler. The trigger should fire only when an UPDATE is performed on the columns in the column list or when an INSERT is performed.

ALTER TABLE hr.employees ADD (time TIMESTAMP WITH TIME ZONE);

CREATE OR REPLACE TRIGGER hr.insert_time_employees
BEFORE 
  INSERT OR UPDATE OF job_id, salary, commission_pct ON hr.employees
FOR EACH ROW
BEGIN
   -- Consider time synchronization problems. The previous update to this 
   -- row might have originated from a site with a clock time ahead of the 
   -- local clock time.
   IF :OLD.TIME IS NULL OR :OLD.TIME < SYSTIMESTAMP THEN
     :NEW.TIME := SYSTIMESTAMP;
   ELSE
     :NEW.TIME := :OLD.TIME + 1 / 86400;
   END IF;
END;
/

If you use such a trigger for conflict resolution, then ensure that the trigger's firing property is fire once, which is the default. Otherwise, a new time might be marked when transactions are applied by an apply process, resulting in the loss of the actual time of the transaction.

See Also:

Oracle Streams Concepts and Administration

9.6.1.1.4 MINIMUM

When a conflict occurs, the MINIMUM conflict handler compares the new value in the LCR from the source database with the current value in the destination database for a designated resolution column. If the new value of the resolution column in the LCR is less than the current value of the column at the destination database, then the apply process resolves the conflict in favor of the LCR. If the new value of the resolution column in the LCR is greater than the current value of the column at the destination database, then the apply process resolves the conflict in favor of the destination database.

If the MINIMUM handler is used for the salary column in the hr.employees table at the dbs2.example.com destination database in the conflict example, then the apply process resolves the conflict in favor of the row LCR, because the salary in the row LCR is less than the current salary in the table. Therefore, after the conflict is resolved, the salary for employee 200 is 4900.

9.6.1.2 Column Lists

Each time you specify a prebuilt update conflict handler for a table, you must specify a column list. A column list is a list of columns for which the update conflict handler is called. If an update conflict occurs for one or more of the columns in the list when an apply process tries to apply a row LCR, then the update conflict handler is called to resolve the conflict. The update conflict handler is not called if a conflict occurs only in columns that are not in the list. The scope of conflict resolution is a single column list on a single row LCR.

You can specify multiple update conflict handlers for a particular table, but the same column cannot be in more than one column list. For example, suppose you specify two prebuilt update conflict handlers on hr.employees table:

• The first update conflict handler has the following columns in its column list: salary and commission_pct.

• The second update conflict handler has the following columns in its column list: job_id and department_id.

Also, assume that no other conflict handlers exist for this table. In this case, if a conflict occurs for the salary column when an apply process tries to apply a row LCR, then the first update conflict handler is called to resolve the conflict. If, however, a conflict occurs for the department_id column, then the second update conflict handler is called to resolve the conflict. If a conflict occurs for a column that is not in a column list for any conflict handler, then no conflict handler is called, and an error results. In this example, if a conflict occurs for the manager_id column in the hr.employees table, then an error results. If conflicts occur in more than one column list when a row LCR is being applied, and there are no conflicts in any columns that are not in a column list, then the appropriate update conflict handler is invoked for each column list with a conflict.

Column lists enable you to use different handlers to resolve conflicts for different types of data. For example, numeric data is often suited for a maximum or minimum conflict handler, while an overwrite or discard conflict handler might be preferred for character data.

If a conflict occurs in a column that is not in a column list, then the error handler for the specific operation on the table attempts to resolve the conflict. If the error handler cannot resolve the conflict, or if there is no such error handler, then the transaction that caused the conflict is moved to the error queue.

Also, if a conflict occurs for a column in a column list that uses either the OVERWRITE, MAXIMUM, or MINIMUM prebuilt handler, and the row LCR does not contain all of the columns in this column list, then the conflict cannot be resolved because all of the values are not available. In this case, the transaction that caused the conflict is moved to the error queue. If the column list uses the DISCARD prebuilt method, then the row LCR is discarded and no error results, even if the row LCR does not contain all of the columns in this column list.

A conditional supplemental log group must be specified for the columns specified in a column list if more than one column at the source database affects the column list at the destination database. Supplemental logging is specified at the source database and adds additional information to the LCR, which is needed to resolve conflicts properly. Typically, a conditional supplemental log group must be specified for the columns in a column list if there are multiple columns in the column list, but not if there is only one column in the column list.

However, in some cases, a conditional supplemental log group is required even if there is only one column in a column list. That is, an apply handler or custom rule-based transformation can combine multiple columns from the source database into a single column in the column list at the destination database. For example, a custom rule-based transformation can take three columns that store street, state, and postal code data from a source database and combine the data into a single address column at a destination database.

Also, in some cases, no conditional supplemental log group is required even if there are multiple columns in a column list. For example, an apply handler or custom rule-based transformation can separate one address column from the source database into multiple columns that are in a column list at the destination database. A custom rule-based transformation can take an address that includes street, state, and postal code data in one address column at a source database and separate the data into three columns at a destination database.

Note:

Prebuilt update conflict handlers do not support LOB, LONG, LONG RAW, user-defined type, and Oracle-supplied type columns. Therefore, you should not include these types of columns in the column_list parameter when running the SET_UPDATE_CONFLICT_HANDLER procedure.

See Also:

• "Specifying Supplemental Logging"

• Oracle Database SQL Language Reference for information about data types

9.6.1.3 Resolution Columns

The resolution column is the column used to identify a prebuilt update conflict handler. If you use a MAXIMUM or MINIMUM prebuilt update conflict handler, then the resolution column is also the column used to resolve the conflict. The resolution column must be one of the columns in the column list for the handler.

For example, if the salary column in the hr.employees table is specified as the resolution column for a maximum or minimum conflict handler, then the salary column is evaluated to determine whether column list values in the row LCR are applied or the destination database values for the column list are retained.

In either of the following situations involving a resolution column for a conflict, the apply process moves the transaction containing the row LCR that caused the conflict to the error queue, if the error handler cannot resolve the problem. In these cases, the conflict cannot be resolved and the values of the columns at the destination database remain unchanged:

• The new LCR value and the destination row value for the resolution column are the same (for example, if the resolution column was not the column causing the conflict).

• Either the new LCR value of the resolution column or the current value of the resolution column at the destination database is NULL.

Note:

Although the resolution column is not used for OVERWRITE and DISCARD conflict handlers, a resolution column must be specified for these conflict handlers.

9.6.1.4 Data Convergence

When you share data between multiple databases, and you want the data to be the same at all of these databases, then ensure that you use conflict resolution handlers that cause the data to converge at all databases. If you allow changes to shared data at all of your databases, then data convergence for a table is possible only if all databases that are sharing data capture changes to the shared data and propagate these changes to all of the other databases that are sharing the data.

In such an environment, the MAXIMUM conflict resolution method can guarantee convergence only if the values in the resolution column are always increasing. A time-based resolution column meets this requirement, if successive time stamps on a row are distinct. The MINIMUM conflict resolution method can guarantee convergence in such an environment only if the values in the resolution column are always decreasing.

9.6.2 Custom Conflict Handlers

You can create a PL/SQL procedure to use as a custom conflict handler. You use the SET_DML_HANDLER procedure in the DBMS_APPLY_ADM package to designate one or more custom conflict handlers for a particular table. Specifically, set the following parameters when you run this procedure to specify a custom conflict handler:

• Set the object_name parameter to the fully qualified name of the table for which you want to perform conflict resolution.

• Set the object_type parameter to TABLE.

• Set the operation_name parameter to the type of operation for which the custom conflict handler is called. The possible operations are the following: INSERT, UPDATE, DELETE, and LOB_UPDATE. You can also set the operation_name parameter to DEFAULT so that the handler is used by default for all operations.

• If you want an error handler to perform conflict resolution when an error is raised, then set the error_handler parameter to TRUE. Or, if you want to include conflict resolution in your procedure DML handler, then set the error_handler parameter to FALSE.

  If you specify FALSE for this parameter, then, when you execute a row LCR using the EXECUTE member procedure for the LCR, the conflict resolution within the procedure DML handler is performed for the specified object and operation(s).

• Specify the procedure to resolve a conflict by setting the user_procedure parameter. This user procedure is called to resolve any conflicts on the specified table resulting from the specified type of operation.

If the custom conflict handler cannot resolve the conflict, then the apply process moves the transaction containing the conflict to the error queue and does not apply the transaction.

If both a prebuilt update conflict handler and a custom conflict handler exist for a particular object, then the prebuilt update conflict handler is invoked only if both of the following conditions are met:

• The custom conflict handler executes the row LCR using the EXECUTE member procedure for the LCR.

• The conflict_resolution parameter in the EXECUTE member procedure for the row LCR is set to TRUE.

See Also:

• Oracle Streams Concepts and Administration for more information about managing error handlers

• Oracle Database PL/SQL Packages and Types Reference for more information about the SET_DML_HANDLER procedure

9.7 Managing Oracle Streams Conflict Detection and Resolution

This section describes the following tasks:

• Setting an Update Conflict Handler

• Modifying an Existing Update Conflict Handler

• Removing an Existing Update Conflict Handler

• Stopping Conflict Detection for Nonkey Columns

See Also:

"Displaying Information About Update Conflict Handlers"

9.7.1 Setting an Update Conflict Handler

Set an update conflict handler using the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package. You can use one of the following prebuilt methods when you create an update conflict resolution handler:

• OVERWRITE

• DISCARD

• MAXIMUM

• MINIMUM

For example, suppose an Oracle Streams environment captures changes to the hr.jobs table at dbs1.example.com and propagates these changes to the dbs2.example.com destination database, where they are applied. In this environment, applications can perform DML changes on the hr.jobs table at both databases, but, if there is a conflict for a particular DML change, then the change at the dbs1.example.com database should always overwrite the change at the dbs2.example.com database. In this environment, you can accomplish this goal by specifying an OVERWRITE handler at the dbs2.example.com database.

To specify an update conflict handler for the hr.jobs table in the hr schema at the dbs2.example.com database, run the following procedure at dbs2.example.com:

DECLARE
  cols DBMS_UTILITY.NAME_ARRAY;
  BEGIN
    cols(1) := 'job_title';
    cols(2) := 'min_salary';
    cols(3) := 'max_salary';
    DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
      object_name       => 'hr.jobs',
      method_name       => 'OVERWRITE',
      resolution_column => 'job_title',
      column_list       => cols);
END;
/

All apply processes running on a database that apply changes to the specified table locally use the specified update conflict handler.

Note:

• The resolution_column is not used for OVERWRITE and DISCARD methods, but one of the columns in the column_list still must be specified.

• You must specify a conditional supplemental log group at the source database for all of the columns in the column_list at the destination database. In this example, you would specify a conditional supplemental log group including the job_title, min_salary, and max_salary columns in the hr.jobs table at the dbs1.example.com database.

• Prebuilt update conflict handlers do not support LOB, LONG, LONG RAW, user-defined type, and Oracle-supplied type columns. Therefore, you should not include these types of columns in the column_list parameter when running the procedure SET_UPDATE_CONFLICT_HANDLER.

See Also:

• "Specifying Supplemental Logging"

• Oracle Streams Extended Examples for an example Oracle Streams environment that illustrates using the MAXIMUM prebuilt method for time-based conflict resolution

• Oracle Database SQL Language Reference for information about data types

9.7.2 Modifying an Existing Update Conflict Handler

You can modify an existing update conflict handler by running the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package. To update an existing conflict handler, specify the same table and resolution column as the existing conflict handler.

To modify the update conflict handler created in "Setting an Update Conflict Handler", you specify the hr.jobs table and the job_title column as the resolution column. You can modify this update conflict handler by specifying a different type of prebuilt method or a different column list, or both. However, to change the resolution column for an update conflict handler, you must remove and re-create the handler.

For example, suppose the environment changes, and you want changes from dbs1.example.com to be discarded in the event of a conflict, whereas previously changes from dbs1.example.com overwrote changes at dbs2.example.com. You can accomplish this goal by specifying a DISCARD handler at the dbs2.example.com database.

To modify the existing update conflict handler for the hr.jobs table in the hr schema at the dbs2.example.com database, run the following procedure:

DECLARE
  cols DBMS_UTILITY.NAME_ARRAY;
  BEGIN
    cols(1) := 'job_title';
    cols(2) := 'min_salary';
    cols(3) := 'max_salary';
    DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
      object_name       => 'hr.jobs',
      method_name       => 'DISCARD',
      resolution_column => 'job_title',
      column_list       => cols);
END;
/

9.7.3 Removing an Existing Update Conflict Handler

You can remove an existing update conflict handler by running the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package. To remove a an existing conflict handler, specify NULL for the method, and specify the same table, column list, and resolution column as the existing conflict handler.

For example, suppose you want to remove the update conflict handler created in "Setting an Update Conflict Handler" and then modified in "Modifying an Existing Update Conflict Handler". To remove this update conflict handler, run the following procedure:

DECLARE
  cols DBMS_UTILITY.NAME_ARRAY;
  BEGIN
    cols(1) := 'job_title';
    cols(2) := 'min_salary';
    cols(3) := 'max_salary';
    DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
      object_name       => 'hr.jobs',
      method_name       => NULL,
      resolution_column => 'job_title',
      column_list       => cols);
END;
/

9.7.4 Stopping Conflict Detection for Nonkey Columns

You can stop conflict detection for nonkey columns using the COMPARE_OLD_VALUES procedure in the DBMS_APPLY_ADM package.

For example, suppose you configure a time column for conflict resolution for the hr.employees table, as described in "MAXIMUM". In this case, you can decide to stop conflict detection for the other nonkey columns in the table. After adding the time column and creating the trigger as described in that section, add the columns in the hr.employees table to the column list for an update conflict handler:

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1)  := 'first_name';
  cols(2)  := 'last_name';
  cols(3)  := 'email';
  cols(4)  := 'phone_number';
  cols(5)  := 'hire_date';
  cols(6)  := 'job_id';
  cols(7)  := 'salary';
  cols(8)  := 'commission_pct';
  cols(9)  := 'manager_id';
  cols(10) := 'department_id';
  cols(11) := 'time';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name       => 'hr.employees',
    method_name       => 'MAXIMUM',
    resolution_column => 'time',
    column_list       => cols);
END;
/

This example does not include the primary key for the table in the column list because it assumes that the primary key is never updated. However, other key columns are included in the column list.

To stop conflict detection for all nonkey columns in the table for both UPDATE and DELETE operations at a destination database, run the following procedure:

DECLARE
  cols DBMS_UTILITY.LNAME_ARRAY;
  BEGIN
    cols(1) := 'first_name';
    cols(2) := 'last_name';
    cols(3) := 'email';
    cols(4) := 'phone_number';
    cols(5) := 'hire_date';
    cols(6) := 'job_id';
    cols(7) := 'salary';
    cols(8) := 'commission_pct';  
  DBMS_APPLY_ADM.COMPARE_OLD_VALUES(
    object_name  => 'hr.employees',
    column_table => cols, 
    operation    => '*',
    compare      => FALSE);
END;
/

The asterisk (*) specified for the operation parameter means that conflict detection is stopped for both UPDATE and DELETE operations. After you run this procedure, all apply processes running on the database that apply changes to the specified table locally do not detect conflicts on the specified columns. Therefore, in this example, the time column is the only column used for conflict detection.

Note:

The example in this section sets an update conflict handler before stopping conflict detection for nonkey columns. However, an update conflict handler is not required before you stop conflict detection for nonkey columns.

See Also:

• "Control Over Conflict Detection for Nonkey Columns"

• "Displaying Information About Conflict Detection"

• Oracle Streams Extended Examples for a detailed example that uses time-based conflict resolution

• Oracle Database PL/SQL Packages and Types Reference for more information about the COMPARE_OLD_VALUES procedure

9.8 Monitoring Conflict Detection and Update Conflict Handlers

The following sections contain queries that you can run to monitor an apply process in a Stream replication environment:

• Displaying Information About Conflict Detection

• Displaying Information About Update Conflict Handlers

See Also:

Oracle Streams Concepts and Administration

9.8.1 Displaying Information About Conflict Detection

You can stop conflict detection for nonkey columns using the COMPARE_OLD_VALUES procedure in the DBMS_APPLY_ADM package. When you use this procedure, conflict detection is stopped for updates and deletes on the specified columns for all apply processes at a destination database. To display each column for which conflict detection has been stopped, run the following query:

COLUMN OBJECT_OWNER HEADING 'Table Owner' FORMAT A15
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A20
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A20
COLUMN COMPARE_OLD_ON_DELETE HEADING 'Compare|Old On|Delete' FORMAT A7
COLUMN COMPARE_OLD_ON_UPDATE HEADING 'Compare|Old On|Update' FORMAT A7

SELECT OBJECT_OWNER, 
       OBJECT_NAME, 
       COLUMN_NAME, 
       COMPARE_OLD_ON_DELETE, 
       COMPARE_OLD_ON_UPDATE 
  FROM DBA_APPLY_TABLE_COLUMNS
  WHERE APPLY_DATABASE_LINK IS NULL;

Your output should look similar to the following:

                                                          Compare Compare
                                                          Old On  Old On
Table Owner     Table Name           Column Name          Delete  Update
--------------- -------------------- -------------------- ------- -------
HR              EMPLOYEES            COMMISSION_PCT       NO      NO
HR              EMPLOYEES            EMAIL                NO      NO
HR              EMPLOYEES            FIRST_NAME           NO      NO
HR              EMPLOYEES            HIRE_DATE            NO      NO
HR              EMPLOYEES            JOB_ID               NO      NO
HR              EMPLOYEES            LAST_NAME            NO      NO
HR              EMPLOYEES            PHONE_NUMBER         NO      NO
HR              EMPLOYEES            SALARY               NO      NO

Note:

You can also stop conflict detection for changes that are applied to remote non-Oracle databases. This query does not display such specifications because it lists a specification only if the APPLY_DATABASE_LINK column is NULL.

See Also:

• "Control Over Conflict Detection for Nonkey Columns"

• "Stopping Conflict Detection for Nonkey Columns"

9.8.2 Displaying Information About Update Conflict Handlers

When you specify an update conflict handler using the SET_UPDATE_CONFLICT_HANDLER procedure in the DBMS_APPLY_ADM package, the update conflict handler is run for all apply processes in the database, when a relevant conflict occurs.

The query in this section displays all of the columns for which conflict resolution has been specified using a prebuilt update conflict handler. That is, it shows the columns in all of the column lists specified in the database. This query also shows the type of prebuilt conflict handler specified and the resolution column specified for the column list.

To display information about all of the update conflict handlers in a database, run the following query:

COLUMN OBJECT_OWNER HEADING 'Table|Owner' FORMAT A5
COLUMN OBJECT_NAME HEADING 'Table Name' FORMAT A12
COLUMN METHOD_NAME HEADING 'Method' FORMAT A12
COLUMN RESOLUTION_COLUMN HEADING 'Resolution|Column' FORMAT A13
COLUMN COLUMN_NAME HEADING 'Column Name' FORMAT A30

SELECT OBJECT_OWNER, 
       OBJECT_NAME, 
       METHOD_NAME, 
       RESOLUTION_COLUMN, 
       COLUMN_NAME
  FROM DBA_APPLY_CONFLICT_COLUMNS
  ORDER BY OBJECT_OWNER, OBJECT_NAME, RESOLUTION_COLUMN;

Your output looks similar to the following:

Table                           Resolution
Owner Table Name   Method       Column        Column Name
----- ------------ ------------ ------------- ------------------------------
HR    COUNTRIES    MAXIMUM      TIME          COUNTRY_NAME
HR    COUNTRIES    MAXIMUM      TIME          REGION_ID
HR    COUNTRIES    MAXIMUM      TIME          TIME
HR    DEPARTMENTS  MAXIMUM      TIME          DEPARTMENT_NAME
HR    DEPARTMENTS  MAXIMUM      TIME          LOCATION_ID
HR    DEPARTMENTS  MAXIMUM      TIME          MANAGER_ID
HR    DEPARTMENTS  MAXIMUM      TIME          TIME

See Also:

"Managing Oracle Streams Conflict Detection and Resolution"