PROBLEM:

After doing insert operation in parallel, when I tried to run the select query it is throwing error ORA-12838.

SQL>  alter session enable parallel dml;

Session altered.

SQL> insert /*+ parallel (test2) */ into test2  select * from dba_objects;

77635 rows created.

SQL> select count(*) from test2;
select count(*) from test2
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

SOLUTION:

 oerr ora 12838
12838, 00000, “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.

After doing any DML activity in parallel, we need to either issue commit or rollback . If we run any other sql query before issuing commit or rollback, it will throw this error.

So let’s issue commit and run the select query,

SQL> commit;

Commit complete.

SQL> select count(*) from test2;

  COUNT(*)
----------
    388175