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)
