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

Here is my very first blog post of the year 2019 about a real issue faced by our development team. To make the blog post short and crisp, let me simply dive into the issue.

Problem Statement: I am using APPEND hint with "INSERT INTO SELECT" statement, but when I am trying to SELECT the data after "INSERT INTO SELECT", it is throwing me "ORA-12838: cannot read/modify an object after modifying it in parallel"

First, check what is ORA-12838-
ORA-12838: 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.

Here is the code snippet to reproduce ORA-12838.

nimish@garg> desc emp
 Name                     Null?    Type
 ------------------------ ------- -------- -----
 EMPNO                    NOT NULL NUMBER(4)
 ENAME                             VARCHAR2(10)
 JOB                               VARCHAR2(9)
 MGR                               NUMBER(4)
 HIREDATE                          DATE
 SAL                               NUMBER(7,2)
 COMM                              NUMBER(7,2)
 DEPTNO                            NUMBER(2)

nimish@garg> create table emp_new as select * from emp where 1=2;
Table created.

nimish@garg> insert /*+ APPEND */ into emp_new select * from emp;
14 rows created.

nimish@garg> select * from emp_new where empno = 7839;
select * from emp_new where empno = 7839
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

As per ORA-12838, Oracle cannot read/modify an object after modifying it in parallel, but we have not used the PARALLEL hint. So we should read about APPEND hint too.

The APPEND hint instructs the optimizer to use direct-path INSERT with the subquery syntax of the INSERT statement.
- Direct-path INSERT is the default in parallel mode.
- In direct-path INSERT, data is appended to the end of the table, rather than using existing space currently allocated to the table. As a result, direct-path INSERT can be considerably faster than conventional INSERT.
- If the table has referential integrity or a trigger, append hint is ignored and Oracle uses the conventional path loading

Now we completely understand "Why INSERT INTO SELECT with APPEND hint is causing ORA-12838". APPEND hint instructs the optimizer to use direct-path INSERT, which is PARALLEL by default and Oracle cannot read/modify an object after modifying it in parallel. The developer was trying to do the same, so before firing the SELECT statement on the TABLE after INSERT INTO SELECT with APPEND hint, he needs to issue a COMMIT which breaks these 2 statements into two transactions.

So now let's try the previous code again, but we will issue the COMMIT before SELECT

nimish@garg> drop table emp_new;
Table dropped.

nimish@garg> create table emp_new as select * from emp where 1=2;
Table created.

nimish@garg> insert /*+ APPEND */ into emp_new select * from emp;
14 rows created.

nimish@garg> commit;
Commit complete.

nimish@garg> select * from emp_new where empno = 7839;
     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7839 KING       PRESIDENT            17-NOV-81    5000.15                    10

Great! it worked. I hope you have Enjoyed reading this post.

Related Links:
- VIEW PUSHED PREDICATE - A Powerful Optimizer Transformation feature
- How to Get Execution Plan and Statistics of SQL Query
- DBMS_PROFILER: How to analyze pl/sql performance
- Top 18 features of Oracle 18c
- Oracle 12c Partitioning New Features - Top 10
- SQL Loader Express Mode - Loading data in Oracle database can't be more easy