- 10 Nov, 2021
- 0 Comments
- 2 Mins Read
ORA-12838: Cannot read/modify an object after modifying it in parallel
ORA-12838: Cannot read/modify an object after modifying it in parallel
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | ankush@thavali> desc empName Null? Type------------------------ ------- -------- -----EMPNO NOT NULL NUMBER(4)ENAME VARCHAR2(10)JOB VARCHAR2(9)MGR NUMBER(4)HIREDATE DATESAL NUMBER(7,2)COMM NUMBER(7,2)DEPTNO NUMBER(2)ankush@thavali> create table emp_new as select * from emp where 1=2;Table created.ankush@thavali> insert /*+ APPEND */ into emp_new select * from emp;14 rows created.ankush@thavali> 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | ankush@thavali> drop table emp_new;Table dropped.ankush@thavali> create table emp_new as select * from emp where 1=2;Table created.ankush@thavali> insert /*+ APPEND */ into emp_new select * from emp;14 rows created.ankush@thavali> commit;Commit complete.ankush@thavalig> select * from emp_new where empno = 7839;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------7839 KING PRESIDENT 17-NOV-81 5000.15 10 |