Hello, Its version 19C of Oracle. We have two transaction/child table (say MAIN_TAB and MAIN_TAB_1 ) which are referring to one parent/reference data table(say REF_TAB1). And the foreign key is indexed in both the child tables. And in this situation while we were doing a disable and enable of constraint on MAIN_TAB from one session, its failing with ora-0054 resource busy error if any INSERT on table MAIN_TAB_1 is running from another session. Is this expected behavior? How we can avoid this?
Below is the test case which reproduce this error:-
drop table REF_TAB1;
CREATE TABLE REF_TAB1( CKEY NUMBER CONSTRAINT REF_TAB1_C01 NOT NULL );
CREATE UNIQUE INDEX REF_TAB1_PK ON REF_TAB1(CKEY);
ALTER TABLE REF_TAB1 ADD ( CONSTRAINT REF_TAB1_PK PRIMARY KEY (CKEY) USING INDEX REF_TAB1_PK ENABLE VALIDATE);
insert into ref_tab1 values(1);
insert into ref_tab1 values(2);
commit;
drop table MAIN_TAB;
CREATE TABLE MAIN_TAB
(
CKEY NUMBER CONSTRAINT MAIN_TAB_C04 NOT NULL,
CSTKEY NUMBER CONSTRAINT MAIN_TAB_C05 NOT NULL
);
ALTER TABLE MAIN_TAB ADD ( CONSTRAINT MAIN_TAB_R01 FOREIGN KEY (CKEY) REFERENCES REF_TAB1 (CKEY)) ;
create index idx1_maintab on MAIN_TAB(CKEY);
insert into MAIN_TAB values(1,1);
insert into MAIN_TAB values(2,2);
commit;
drop table MAIN_TAB_1;
CREATE TABLE MAIN_TAB_1
(
CKEY NUMBER CONSTRAINT MAIN_TAB_C06 NOT NULL,
CSTKEY NUMBER CONSTRAINT MAIN_TAB_C07 NOT NULL
);
ALTER TABLE MAIN_TAB_1 ADD ( CONSTRAINT MAIN_TAB_R02 FOREIGN KEY (CKEY) REFERENCES REF_TAB1 (CKEY)) ;
create index idx1_maintab_1 on MAIN_TAB_1(CKEY);
From session- 1 :-
alter table MAIN_TAB modify constraint MAIN_TAB_R01 disable;
from session-2 :-
insert into MAIN_TAB_1 values(1,1);
insert into MAIN_TAB_1 values(2,2);
Now from session 1:-
alter table MAIN_TAB enable novalidate constraint MAIN_TAB_R01;
It error out with "ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired".