Oracle Distributed Systems

Chapter 14. Procedural Replication

The row-level, or multi-master, component of Oracle’s advanced replication facilities were never intended to support transactions that modify numerous records. Instead, using procedural replication you can write PL/SQL procedures around such operations and replicate calls to the procedures instead of to the row-level transactions.

When to Use Procedural Replication

There is no hard limit on how many records a single transaction can modify in a table that is undergoing row-level replication, but as a general rule, modifying more than about 100 records in a single transaction is not advisable, at least not on a regular basis. Bear in mind that even though you may use a single transaction to modify multiple records, Oracle queues an RPC for each modified record. Before you know it, the deferred transaction queue may have thousands of entries.

Typical operations that are ideal candidates for procedural replication include the following:

Batch updates

For example, a transaction that adjusts the price of all items in a catalog

Data purging

For example, deleting all records that are older than a certain date

Data archiving

For example, moving sales records from the previous quarter into an archive table

Specialty operations

For example, creating or dropping a user in multiple databases (see the example in this chapter)

This list is by no means exhaustive. Row-level replication is best suited for transactions that modify a single record. Consider procedural replication ...