PROBLEM:

We did one DML transaction without parallel, and in the same session, when we did another DML transaction with parallel, we got error as ORA-12839.

SQL> ALTER session enable parallel dml;

Session altered.

SQL> insert into test2 select * from dba_objects;

77635 rows created.

SQL> delete  /*+ parallel (test2) */ from test2;
delete  /*+ parallel (test2) */ from test2
*
ERROR at line 1:
ORA-12839: cannot modify an object in parallel after modifying it

SOLUTION:

We need to issue, COMMIT or ROLLBACK, before running that parallel DML query.

oerr ora 12839
12839, 00000, “cannot modify an object in parallel after modifying it”
// *Cause: Within the same transaction, an attempt was made to perform
// parallel modification operations on a table after it had been modified.
// This is not permitted.
// *Action: Rewrite the transaction or break it up into two transactions:
// one containing the parallel modification and the second containing the
// initial modification operation.

SQL> delete  /*+ parallel (test2) */ from test2;
delete  /*+ parallel (test2) */ from test2
*
ERROR at line 1:
ORA-12839: cannot modify an object in parallel after modifying it


SQL> commit;

Commit complete.

SQL> delete  /*+ parallel (test2) */ from test2;

1319795 rows deleted.