The ORA-12841: Cannot Alter The Session Parallel DML State Within A Transaction error comes when you issue alter session enable parallel DML command when some transaction like an update, insert, delete is in progress. A transaction completes when you either issue Commit or Rollback.
As per Oracle Error Message Guide,
ORA-12841: Cannot alter the session parallel DML state within a transaction
Cause: Transaction in progressAction: Commit or rollback transaction and then re-execute
Let’s reproduce the ORA-12841 error,
SQL> INSERT INTO DUMMY SELECT * FROM dual; 1 rows created
Here a row is inserted in a dummy table. Now let’s run alter command.
SQL> ALTER SESSION ENABLE PARALLEL DML; ERROR: ORA-12841: Cannot alter the session parallel DML state within a transaction
The alter command failed with the ORA-12841 error.
Commit or Rollback transaction and then re-execute the alter session command to enable parallel mode in Oracle Database to resolve-12841: Cannot Alter The Session Parallel DML State Within A Transaction.
So let’s commit the session and run again the alter command. You can issue rollback command as well instead of commit.
SQL> COMMIT; Commit complete. SQL> ALTER SESSION ENABLE PARALLEL DML; Session altered.
Now alter command is successful and parallel mode is enabled.
A parallel mode can’t be set in the Oracle database unless you Commit or rollback transactions.
I hope you found this article helpful. Please share and subscribe.
Further Reading
