Add foreign constraint in child table at a time inserting statement fire in references table(parent table) so inserting query is going to hold, but we want to insert statement and add foreign key constraint parallel.
it is possible or not ?
So, if you have an uncommitted insert on the parent and attempt to create an FK on the child by default it'll fail as you say.
For example, take these two tables:
create table t1 (
x int not null primary key
);
create table t2 (
x int
);
In session 1 run:
SQL> insert into t1 values (1);
1 row created.
And in session 2:
SQL> alter table t2 add constraint fk foreign key (x) references t1(x);
alter table t2 add constraint fk foreign key (x) references t1(x)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
BOOOM!
Fortunately there is a way around this (at least from 11.2; I haven't checked on earlier releases). Create the FK novalidated:
SQL> alter table t2 add constraint fk foreign key (x) references t1(x) novalidate;
This is blocked until you commit/rollback the insert in session 1. But you can insert new rows in other sessions. In session 3:
SQL> insert into t1 values (2);
1 row created.
So your application can continue. As soon as the first insert commits, the DDL will complete too:
Session 1:
SQL> commit;
Commit complete.
Session 2:
Table altered.
SQL>
Note that a novalidated constraint doesn't check existing rows in the child. So it's possible to have orphaned rows when you do this!