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.