ORA-02292 in target when UPDATE operation modifying the partition key column issued (4244116)

When a child table has a referential integrity constraint for the parent table, UPDATE operation in source could cause ORA-02292 error in target.

-- TARGET
-- event_log

Error    2013-05-13 16:59:57.738581 4230 1095809344 s:2 Poster: 12070 - OCITransCommit failed with ORA-2091.  (posting from src, queue source, to target) [module osp]
Error    2013-05-13 16:59:57.738839 4230 1095809344 s:2 Poster: failed to commit transaction session 2 in CommitFunc(): ORA-02091: transaction rolled back.ORA-02292: integrity constraint (SCOTT.FK1IESV_PURCHASE_INFO_PM) violated - child record found. Will exit Post..  (posting from src, queue source, to target) [module opo]
Notice   2013-05-13 16:59:57.739952 4230 15013584 Poster: Last committed SCN 21750334.  (posting from src, queue source, to target) [module opo]
Info     2013-05-13 16:59:57.743666 3376 2431998352 Poster exited with code=1, pid = 4230  (posting from src, queue source, to target)

Let's go over the detail with simple test

1. Constraint

SCOTT.IESV_PURCHASE_INFO
 :: PRIMARY KEY (PURCHASE_IDX)
 
SCOTT.IESV_PURCHASE_INFO_PM 
 
 :: ALTER TABLE SCOTT.IESV_PURCHASE_INFO_PM ADD CONSTRAINT XPKIESV_PURCHASE_INFO_PM PRIMARY KEY (PURCHASE_IDX);
 :: ALTER TABLE SCOTT.IESV_PURCHASE_INFO_PM ADD CONSTRAINT FK1IESV_PURCHASE_INFO_PM FOREIGN KEY (PURCHASE_IDX) REFERENCES SCOTT.IESV_PURCHASE_INFO (PURCHASE_IDX) NOVALIDATE;
        
2. source/target initial data

INSERT INTO SCOTT.IESV_PURCHASE_INFO (PURCHASE_IDX, ID_USER_SERVICE, PURCHASE_STATE, PURCHASE_DATE)
VALUES(1,'USERID_1','Y',SYSDATE)
/
COMMIT;

INSERT INTO SCOTT.IESV_PURCHASE_INFO_PM (PURCHASE_IDX)
VALUES(1)
/
COMMIT;

3.  SOURCE UPDATE operation modifying the partition key(NO problem)

UPDATE IESV_PURCHASE_INFO
SET PURCHASE_DATE = SYSDATE - 800
/
COMMIT;

4. Qview in target

qview> oread 0 100
Sque 2, mtime 05/13/13 16:59:57, qseq 1886, 05/13/13 16:59:56, 315/5502480, transid 2, Update, AAAS/OAAEAAAPi6AAA  "SCOTT"."IESV_PURCHASE_INFO", SCN  21754859, forward = 1
Sque 2, mtime 05/13/13 16:59:57, qseq 2300, 05/13/13 16:59:56, 315/5503004, transid 2, Delete, AAAS/OAAEAAAPi6AAA  "SCOTT"."IESV_PURCHASE_INFO", SCN  21754859, forward = 1
Sque 2, mtime 05/13/13 16:59:57, qseq 2666, 05/13/13 16:59:57, 315/5514768, transid 2, Insert, AAAS+0AAEAAAP06AAA  "SCOTT"."IESV_PURCHASE_INFO", SCN  21754868, forward = 1
Sque 2, mtime 05/13/13 16:59:57, qseq 3083, 05/13/13 16:59:57, 315/5516180, transid 2, COMMIT, AAAS+0AAEAAAP06AAA  , SCN  21754869, forward = 3

5. For avoiding this issue, you may want to disable the FK constraint in target.

SQL> alter table IESV_PURCHASE_INFO_PM drop constraint FK1IESV_PURCHASE_INFO_PM;

After that, you can see the SQL statement below accordingly but it doesn't have any problem.

set pagesize 500
set linesize 190
col module for a30
col "SQL" for a40
col "C_N" for 99

select a.sql_id,a.plan_hash_value,a.child_number "C_N",
    substr(a.sql_text,1,40) "SQL",
    a.executions,module,
    rows_processed, 
    elapsed_time "Elapsed(ms)", 
    trunc(elapsed_time/a.executions/1000000,6) "AVG_E(s)"
from v$sql a
where upper(a.sql_fulltext) like '%IESV_PURCHASE_INFO%'

order by "AVG_E(s)" desc
/

SQL_ID        PLAN_HASH_VALUE C_N SQL                                      EXECUTIONS MODULE                         ROWS_PROCESSED Elapsed(ms)   AVG_E(s)
------------- --------------- --- ---------------------------------------- ---------- ------------------------------ -------------- ----------- ----------
btx2zzfn5jfg8      3848440781   0 update   "SCOTT"."IESV_PURCHASE_INFO" t           1 Post-2100-source-src                        1       50773    .050773
gny7wwf7a9jq8      3445498623   0 delete /*+ RESTRICT_ALL_REF_CONS */   fr          1 Post-2100-source-src                        1        1658    .001658
4q1xh74989gdy               0   0 insert into "SCOTT"."IESV_PURCHASE_INFO"          1 Post-2100-source-src                        1        1200      .0012

select * from table(dbms_xplan.display_cursor('btx2zzfn5jfg8',null,'basic'));

update   "SCOTT"."IESV_PURCHASE_INFO" t 
set "PURCHASE_DATE"=:V001 
where rownum = 1 and "PURCHASE_DATE"=:V002 and "PURCHASE_IDX"=:V003

select * from table(dbms_xplan.display_cursor('gny7wwf7a9jq8',null,'basic'));

delete /*+ RESTRICT_ALL_REF_CONS */   
from "SCOTT"."IESV_PURCHASE_INFO" t 
where rownum = 1  and "PURCHASE_IDX"=:V001

select * from table(dbms_xplan.display_cursor('4q1xh74989gdy',null,'basic'));

insert into "SCOTT"."IESV_PURCHASE_INFO" ("PURCHASE_IDX","ID_USER_SERVICE","PURCHASE_DATE","PURCHASE_STATE") values (:V001,:V002,:V003,:V004)