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

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

Error: 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.

Example:
Consider one table TEST10 and tried to insert rows in it with parallel(PARALLEL) or DIRECT path method (APPEND_VALUES) hints.
It is generating error while i tried to Select the query as show in example. To Provide solution you need to commit first after the parallel or direct method.

Error

-- Enable Parallel session for DML operations
SQL> alter session enable parallel dml;
Session altered.

-- Try any one parallel or direct path method for insert operation.
SQL> insert /*+ PARALLEL (test10) */ into test10 select * from dba_objects;
OR
SQL> insert /*+ APPEND_VALUES */ into test10 select * from dba_objects;

-- Run the select query will generate error
SQL> select count(*) from test10;
select count(*) from test10
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Solution
Commit statement will fixed the error ORA-12838

SQL>commit;
Commit complete.

SQL> select count(*) from test10;
COUNT(*)
----------
218949


Leave a Reply