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
