preventing NOWAIT locks in DDL commands

Hi!

Suppose there is a referential constraint between A (parent) and B (child). Then if one tries to delete from A, Oracle will have to check that no children in B exist for the record being deleted from A.

If such deletions are massive, one could disable the constraint to avoid these checks and thus speed them up.

So, the application disables the constraint, deletes the records, and then reenables the constraint. We assume that the application is trusted not to violate the constraint.
The application reenables the constraint:

ALTER TABLE B ENABLE CONSTRAINT A_FK; Now suppose that another program modifies either A or B. Suppose that this other program is also trusted not to violate the integrity constraint.

The problem is that whenever the first program attempts to issue the above DDL and the second program has an uncommitted transaction that locked A or B, the first program gets the following error:

ORA-00054 resource busy and acquire with NOWAIT specified

It appears to be impossible to give the above DDL an option not to use NOWAIT in the attempt to acquire the lock.

It also appears to be of little use to LOCK TABLE command before issuing the DDL -- because any DDL commits (thus releasing the lock) before and after it executes.

Is it possible to make the DDL (the ALTER TABLE...) command wait for the lock to become available?

Thank you.
k& Received on Fri Jul 10 1998 - 13:51:02 CDT