ORA-12839: cannot modify an object in parallel after modifying

Mappings Fail with ORA-12839 Ater Upgrade Oracle Database From 10.2.0.2 To 10.2.0.5 [ID 1314402.1]

Symptoms

After upgrade of Oracle Database from Oracle 10.2.0.2 to 10.2.0.5, following error occurs when running OWB mappings:

ORA-12839: cannot modify an object in parallel after modifying

Changes

Upgrade from Oracle 10.2.0.2 to 10.2.0.5.

Cause

The issue can be caused by audit tables having parallel DML enabled.

1. Oracle 10.2.0.2 -> No error

The ORA-12839 error does not occur in Oracle 10.2.0.2 because despite the audit tables have Parallel DML enabled, the DB optimizer decides to run the DELETE serial. The reason that the 10.2.0.2 optimizer makes this decision is because of a restriction that existed in 10.2.0.2 that prevented parallel DML in recursive SQL. (Recursive SQL is e.g. SQL executed from a Package like an OWB Mapping)


2. Oracle 10.2.0.5 -> ORA-12839

An Enhancement Request (7022700) is introduced in 10.2.0.5. This ER allows that more recursive SQL DML can run in parallel. So the 10.2.0.5 optimizer finds the Audit table configured with Parallel and decides to run the DELETE with parallel DML. But the Oracle parallel DML rule is to allow only one DML in one transaction. And since OWB mappings can have two DMLs (INSERT+DELETE) in one transaction, the ORA-12839 occurred is expected. The ORA-12839 should not occur when the OWB audit table is configured with NOPARALLEL, which is the default.

Solution

Disable Parallel DML on all OWB Audit Tables. This is the default and tested configuration.

ALTER TABLE <table_name> NOPARALLEL;