O'Reilly logo

Oracle Distributed Systems by Charles Dye

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Types of Conflicts Detected

Oracle detects conflicts based on PL/SQL exceptions, as summarized in Table 15.1, only at the destination site. Note that conflict detection does not imply conflict resolution.

Table 15-1. Detectable PL/SQL Exceptions

Type of DML

Potential Conflicts

INSERT

DUP_VAL_ON_INDEX

UPDATE

SQL%ROWCOUNT = 0 (NO_DATA_FOUND)

SQL%ROWCOUNT > 1 (TOO_MANY_ROWS)

DUP_VAL_ON_INDEX

DELETE

SQL%ROWCOUNT = 0 (NO_DATA_FOUND)

SQL%ROWCOUNT > 1 (TOO_MANY_ROWS)

The situations for which Oracle does not resolve conflicts include:

  • Deletes that raise NO_DATA_FOUND errors (even though they are detected)

  • Deletes that raise TOO_MANY_ROWS errors

  • Use of NULL values in columns used for conflict resolution

  • DML that violates referential integrity constraints

  • Conflicts arising from procedural replication

Why not? A brief analysis of Oracle’s implementation reveals why these restrictions must exist.

Limitations of Delete Conflict Resolution

Because of the difficulties of processing delete conflicts, Oracle’s recommendation is to design replicated applications to flag records as deleted. Include a STATUS column in the table and update it to D, for example, instead of actually deleting the row. This way, you can avoid all potential delete conflicts and avoid the task of writing your own delete conflict handling procedure. You can perform the actual delete at scheduled intervals using procedural replication. If this is not an option for your application, then consider the following alternatives.

If a row deleted ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required