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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.