how to online reorg oracle tables with active replication trigger?

167 Views Asked by At

We have an oracle database where several tables are replicated to remote application using triggers (insert, delete, update). We would like to reorganize these tables using DBMS_REDEFINITION.

My problem is that if I activate the triggers on the interim table then all changes will be logged twice. If I activate triggers after calling finish_redef_table then some changes could be lost.

Is there a way to avoid these issues?

EDIT:

Here is a simplified code. Original table is EMP with index IMP_I and trigger IMP_T that logs table changes in other table (that cannot be renamed):

CREATE TABLE EMP_TMP AS SELECT * from EMP where ROWNUM=0;
-- starting the online copy:
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT', 'EMP', 'EMP_TMP');
-- most data are copied so re-creating index and trigger
CREATE INDEX IMP_I_TMP ON TABLE EMP_TMP ...
CREATE TRIGGER IMP_T_TMP BEFORE UPDATE ON EMP_TMP FOR EACH ROW ...
ALTER TRIGGER IMP_T_TMP ENABLE
-- apply the table changes since copy started and swap table names:
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'EMP', 'EMP_TMP');
-- renaming original to old and new to original
ALTER TABLE 'EMP_TMP' RENAME INDEX IMP_I TO IMP_I_OLD;
ALTER TABLE 'EMP' RENAME INDEX IMP_I_TMP TO IMP_I;
ALTER TRIGGER 'EMP_T' RENAME TO 'EMP_T_OLD';
ALTER TRIGGER 'EMP_T_TMP' RENAME TO 'EMP_T';

The trigger on new table can be activated before or after calling FINISH_REDEF_TABLE procedure but in my opinion both could cause inconsistency.

0

There are 0 best solutions below