Re: Integrity constraint
Date: Wed, 4 Feb 2009 15:46:57 -0000
"vm22" <vivekmarwaha_at_gmail.com> wrote in message news:00c5c91c-15bc-4c8d-b835-d04c7b939caa_at_e1g2000pra.googlegroups.com...
Sorry Jon - ignore my last post.
It does seem like we are inserting while the delete is running. I am really surprised that Oracle does not ignore the new records that are added but thanks for the great explanation nevertheless.
What is the solution to this - I looked at the asktom but could not find anything.
The simplest change is to lock the Y and Z tables before you start the delete. But then the other processes will probably crash - on the other hand what would they do if they tried to insert data just AFTER your delete had completed ?
Since you're on 10.2 I thnk an alternative
would be to use the "log errors" clause
with "reject limit unlimited". You need to precreate an error logging table with the dbms_errlog.create_error_log prcedure first, though. This would allow you to
identify the rows that had acquired new
child data between the start of the delete and the moment you got to the row.
I've just set up a little model on 10.2.0.1 to see if my hypothesis was correct, and (provided the FK indexes are in place), I can cause the problem to appear on demand:
DELETE /*+ gather_plan_statistics */ FROM x *
ERROR at line 1:
ORA-02292: integrity constraint (TEST_USER.SYS_C0035144) violated - child record
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlReceived on Wed Feb 04 2009 - 09:46:57 CST