Hello!
I faced the enq: TM contention wait during direct-path (append hint) multi-table insert and the operation never completes. Once I take away the append hint everthing got working fine. Please see below the code.
I tried to find any particularities regarding direct-path multi-table insert but failed.
Could someone please explaon why it is happening, please?
DECLARE
v_table1_sid NUMBER;
BEGIN
----------------
v_table1_sid:= table1_seq.NEXTVAL;
INSERT /*+ append */
ALL
WHEN ROWNUM = 1
THEN
INTO table1(table1_PK, .....,.....,.... )
VALUES ( v_table1_sid,......,.........)
WHEN 1 = 1
THEN
INTO table2(table2_PK, table1_PK,.......,.......,.....)
VALUES (table2_seq.NEXTVAL, v_table1_sid)
WHEN 1 = 1
THEN
INTO table3(table3_PK, table1_PK,.......,.......,.....)
VALUES (table3_seq.NEXTVAL, v_table1_sid)
SELECT..................
There are no foreighn keys on table3 and table2 referring to table1.table1_PK. table2.table1_PK and table3.table1_PK it are just logical FKs.
The last statement wich appears in the session, and it seems to get hung, is SELECT table1_seq.NEXTVAL FROM DUAL