Managing Deferred Transactions

Typically, Oracle replication is configured to push and purge the deferred transaction queue automatically. At times, however, you may need to push or purge the deferred transaction queue manually. The following sections contain examples for pushing and purging the deferred transaction queue at a snapshot site, but the process is the same at master sites.

Pushing the Deferred Transaction Queue

If you do not automatically propagate the transactions in your deferred transaction queue during the refresh of your snapshot, you must complete the following steps to propagate changes made to the updateable snapshot to its master table.

--The following procedures must be executed by the snapshot administrator
--at the remote snapshot site.

CONNECT snapadmin/snapadmin@snap1.world

--Propagation of the deferred transaction queue is based on the destination of 
--the transaction. Execute the following SELECT statement to view the deferred 
--transactions and their destinations. Each distinct destination and the number
--of transactions pending for the destination wil be displayed:

SELECT DISTINCT(dblink), COUNT(deferred_tran_id) 
   FROM deftrandest GROUP BY dblink;

--You need to execute the DBMS_DEFER_SYS.PUSH function for each master
--site that is listed as a destination for a deferred transaction.  

DECLARE
   temp INTEGER;
BEGIN
   temp := DBMS_DEFER_SYS.PUSH (
      DESTINATION => 'orc1.world',
      STOP_ON_ERROR => FALSE,
      DELAY_SECONDS => 0,
      PARALLELISM => 0);
END;
/

--Repeat the above procedure for each destination that was returned in the above 
--SELECT statement.

Purging the Deferred Transaction Queue

If you do not automatically purge the successfully propagated transactions in your deferred transaction queue periodically, you must complete the following steps to purge them manually.

--The following procedures must be executed by the snapshot administrator
--at the remote snapshot site.

CONNECT snapadmin/snapadmin@snap1.world

DECLARE
   temp INTEGER;
BEGIN
   temp := DBMS_DEFER_SYS.PURGE (
      PURGE_METHOD => purge_method_quick);
END;

/