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  /

3 Comments

  1. In my case I have an update without a hint clause inside a loop, and the commit is outside. Got the same error. So I think it is not for the append hit clause. I think this is a parallel processing problem.

  2. and I was refusing to put commit inside the loop after any dml because of performance.. If loop processes 2000 records it will write down to disk 2000 times.. performance will become a problem as data grows inside the cursor or loop.

    1. Hi Antonio, Thanks for reading this post. Yes you are correct. If an object is modified in parallel, we get the error due to constraints. But in my case, using direct path load caused this error because by default oracle uses parallel for direct path load.

Leave a Reply