Create Conflict Avoidance Methods for Delete Conflicts

Unlike update conflicts, where there are two values to compare, simply deleting a row makes the update conflict resolution methods described in the previous section ineffective because only one value would exist.

The best way to deal with deleting rows in a replication environment is to "avoid" the conflict by marking a row for deletion and periodically purging the table of all "marked" records. Because you are not physically removing this row, your data can converge at all master sites if a conflict arises because you still have two values to compare, assuming that no other errors have occurred. After you are sure that your data has converged, you can purge "marked" rows using a replicated purge procedure.

When you are developing your front-end application for your database, you probably want to "filter out" the rows that have been marked for deletion, because doing so makes it appear to your users as though the row was physically deleted. Simply exclude the rows that have been marked for deletion in the SELECT statement for your data set. For example, a select statement for a current employee listing might be similar to the following:

SELECT * FROM emp WHERE remove_date IS NULL;

This section describes how to prepare your replicated table to avoid delete conflicts. You also see how to use procedural replication to purge those records that have been "marked" for deletion.

CONNECT repadmin/repadmin@orc1.world

BEGIN
   DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/

--You must add a column to your replicated table that stores the
--mark for deleted records. It is advisable to use a timestamp to mark your
--records for deletion (timestamp reflects when the record was marked for
--deletion). Because you are using a timestamp, your new column must be
--a DATE datatype.  Use the DBMS_REPCAT.ALTER_MASTER_REPOBJECT procedure to add
--the REMOVE_DATE column to your existing replicated table.

BEGIN
   DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
      sname => 'SCOTT',
      oname => 'EMP',
      type => 'TABLE',
      ddl_text => 'ALTER TABLE scott.emp ADD (remove_date DATE)');
END;
/

--After you have inserted a new column into your replicated object,
--make sure that you regenerate replication support for
--the affected object. This step should be performed immmediately
--after you alter the replicated object.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'EMP', 
      type => 'TABLE',
      min_communication => TRUE); 
END;
/

--The following package is replicated to all of the master sites in your
--replication environment. This package purges all "marked" records from
--the specified table.

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'SCOTT_MG',
      type => 'PACKAGE',
      oname => 'PURGE',
      sname => 'SCOTT',
      ddl_text => 'CREATE OR REPLACE PACKAGE scott.purge AS
                      PROCEDURE remove_emp(purge_date DATE);
                   END;');
END;
/

BEGIN
   DBMS_REPCAT.CREATE_MASTER_REPOBJECT (
      gname => 'SCOTT_MG',
      type => 'PACKAGE BODY',
      oname => 'PURGE',
      sname => 'SCOTT',
      ddl_text => 'CREATE OR REPLACE PACKAGE BODY scott.purge AS
                      PROCEDURE remove_emp(purge_date IN DATE) IS
                      BEGIN
                         DBMS_REPUTIL.REPLICATION_OFF;
                         LOCK TABLE scott.emp IN EXCLUSIVE MODE;
                         DELETE scott.emp WHERE remove_date IS NOT NULL AND
                            remove_date < purge_date;
                         DBMS_REPUTIL.REPLICATION_ON;
                      EXCEPTION WHEN others THEN
                         DBMS_REPUTIL.REPLICATION_ON;
                      END;
                   END;');
END;
/

--After you have created your package (package and package body), generate 
--replication support for each component. After you generate
--replication support, a synonym is created for you and added to your
--master group as a replicated object. This synonym is labeled as
--DEFER_PURGE.REMOVE_EMP.

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'PURGE', 
      type => 'PACKAGE',
      min_communication => TRUE); 
END;
/

BEGIN 
    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
      sname => 'SCOTT',
      oname => 'PURGE', 
      type => 'PACKAGE BODY',
      min_communication => TRUE); 
END;
/

--After replication support has been regenerated, resume replication
--activity by using the RESUME_MASTER_ACTIVITY procedure API.

BEGIN
   DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
      gname => 'SCOTT_MG');
END;
/