Hello,

we use oracle 11.2.0.3 and following topic i would like to discuss here:

Setup like this:

Table A (partitioned) with an article column and FK defined to masterdata (article) keytable X

Table B also with article column and FK defined to masterdata (article) keytable X

keytable X is referenced by table A and B

Procedure proc (aggregation program) running and inserting new records in Table B.
While procedure running, there are active locks on keytable X in Mode 3.

Now the problem. Another procedure proc2 started and want to lock table A (partition in exclusive mod nowait).
This failed with error: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

I've narrowed down the problem.
Disabling the FK on Table A before running proc2 do not help. Error message also occur.
Disabling the FKs on Table B before running proc2 helps and proc2 runs fine.

I can reproduce this problem very easy. While running proc, i'm not able to enable/create any addional FK on any Table
which references also keytable X

Question: is this really a normal and expected behaviour?

Thanks a lot in advance

create table t1 (
  x int primary key
);
create table t2 (
  x int references t1(x)
);
create table t3 (
  x int references t1(x)
) partition by range (x) (
  partition p0 values less than (11)
);

insert into t1 values (1);
commit;

SQL> insert into t2 values (1);

1 row created.

SQL> lock table t3 partition for (1) in exclusive mode;

Table(s) Locked.

SQL> insert into t3 values (1);

1 row created.

select * from dba_dml_locks;

SESSION_ID  OWNER  NAME  MODE_HELD   MODE_REQUESTED  LAST_CONVERT  BLOCKING_OTHERS  
96          CHRIS  T1    Row-X (SX)  None            588           Not Blocking     
96          CHRIS  T2    Row-X (SX)  None            589           Not Blocking     
47          CHRIS  T3    Row-X (SX)  None            348           Not Blocking     
47          CHRIS  T3    Exclusive   None            348           Not Blocking     
47          CHRIS  T1    Row-X (SX)  None            219           Not Blocking