Re: Integrity constraint

"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.

Regards,
Vivek

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:

SQL> /
DELETE /*+ gather_plan_statistics */ FROM x *
ERROR at line 1:
ORA-02292: integrity constraint (TEST_USER.SYS_C0035144) violated - child record
 found

-- 
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.html