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

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

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.

<b>Example:</b>
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.

<b>Error</b>
<code>
— 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
</code>

<b>Solution</b>
Commit statement will fixed the error ORA-12838
<code>
SQL>commit;
Commit complete.

SQL> select count(*) from test10;

COUNT(*)
———-
218949
</code>

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.