ORA-12841: Cannot Alter The Session Parallel DML State Within A Transaction

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.

What is ORA-12841?

As per Oracle Error Message Guide,

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

Cause: Transaction in progress
Action: 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.

ORA-12841 Solution

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