Tag Archives: run transaction in parallel

ORA-12841: Cannot alter the session parallel DML state within a transaction

ORA-12841: Cannot alter the session parallel DML state within a transaction

Error
ORA-12841: Cannot alter the session parallel DML state within a transaction

Cause
Cause Transaction in progress.
Example of cause:
Try to enable the Parallel DML operation with alter command after starting the transaction.

SQL> insert /*+ parallel (test10) */ into test10 select * from dba_objects;
72983 rows created.

SQL> select count(*) from test10;
COUNT(*)
---------
145966

SQL> alter session enable parallel dml;
ERROR:
ORA-12841: Cannot alter the session parallel DML state within a transaction

Solution
Action: Commit or rollback transaction and then re-execute
Example of Solution:
We are enabling parallel DML option after doing some transaction then we need to issue COMMIT or ROLLBACK before enabling DML parallel.

SQL> alter session enable parallel dml;
ERROR:
ORA-12841: Cannot alter the session parallel DML state within a transaction

SQL> commit;
Commit complete.

SQL> alter session enable parallel dml;
Session altered.

Advertisements