SymptomsAfter 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
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.
ALTER TABLE <table_name> NOPARALLEL;