ORA-12838: cannot read/modify an object after modifying it in parallel

ORA-12838: cannot read/modify an object after modifying it in parallel

Cause:

The error occur when multiple session try to load data on the same table with direct path load operation + parallel

SQL> begin
  2  for i in 1 .. 100000 loop
  3  insert /*+ append */ into sales select * from demo_dup;
  4  end loop;
  5  commit;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
ORA-06512: at line 3

[oracle@exdbadm01 ~]$ oerr ora 12838
12838, 00000, "cannot read/modify an object after modifying it in parallel"
// *Cause: Within the same transaction, an attempt was made to add read or
// modification statements on a table after it had been modified in parallel
// or with direct load. This is not permitted.
// *Action: Rewrite the transaction, or break it up into two transactions:
// one containing the initial modification and the second containing the
// parallel modification operation.

Workaround:

There are no constraints on the table where data load happen so constraints error were skipped and ora 12838 is thrown.Four sessions were connected to same database and all sessions perform insert with append option

kish@exdbx<>select a.sid,a.serial#,a.username,a.sql_id,a.event,b.sql_text from v$session a,v$sql b where a.sql_id=b.sql_id;

                                     SID                                  SERIAL# USERNAME                       SQL_ID        EVENT
---------------------------------------- ---------------------------------------- ------------------------------ ------------- ----------------------------------------------------------------
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


                                      36                                      133 TEST                           b08gc4pv7cprr enq: TM - contention
INSERT /*+append */ INTO SALES SELECT * FROM DEMO_DUP


                                      71                                      657 TEST                           b08gc4pv7cprr enq: TM - contention
INSERT /*+append */ INTO SALES SELECT * FROM DEMO_DUP

Execute the insert with no append because by default direct path load work in parallel and rows are appended above highwatermark ignoring the free space gaps below.If constraints are present on the table,then oracle force conventional data load to maintain consistency

Either exclude append hint from the insert or run one insert with append ,commit it and then go for next insert and so on

You know the append hint would insert the records above the high watermark ignoring the free space below which were deleted

Session 1:

SQL> begin
  2  for i in 1 .. 100000 loop
  3  insert /*+ append */ into sales select * from demo_dup;
  4  end loop;
  5  commit;
  6  end;
  7  /

commited

After transaction 1 commit from session 1,run next transaction in session 2
Session 2:


SQL> begin
  2  for i in 100000 .. 100001 loop
  3  insert /*+ append */ into sales select * from demo_dup;
  4  end loop;
  5  commit;
  6  end;
  7  /