CODEWITHSUNDEEP
oracle-databaseoracle-autonomous-db
Hermann Baer
Hermann Baer

Reputation: 126

ORA-12839 when I from parallel DML in my ATP instance?

I am testing ATP with my application and get the following error:

ORA-12839 Cannot Modify An Object In Parallel After Modifying It.

Is there any way to disable the parallel DML on the ATP without making changes to the application code?

DROP TABLE objects PURGE;
CREATE TABLE objects
  AS
    SELECT *
      FROM user_objects;

UPDATE /*+ parallel (objects) */ objects
   SET
  object_id = object_id + 1000;

SELECT *
  FROM objects;

enter image description here

Upvotes: 1

Views: 1241

Answers (1)

Hermann Baer
Hermann Baer

Reputation: 126

Do NOT use the HIGH or MEDIUM service, where parallelism is built-in and configured out of the box without you actively enabling it.

You should either use the transactional services (LOW, TP, TPURGENT) or you can disable parallel DML using “alter session disable parallel dml”.

Here is the same script, running on the LOW service -

select sys_context('userenv', 'service_name') from dual;
 

DROP TABLE objects PURGE;
CREATE TABLE objects
  AS
    SELECT *
      FROM user_objects;

UPDATE /*+ parallel (objects) */ objects
   SET
  object_id = object_id + 1000;

SELECT *
  FROM objects;

enter image description here

But wait, what are these 'LOW' or 'HIGH' services?

(Docs)

Note the words 'parallel' in the descriptions -

The basic characteristics of these consumer groups are:

HIGH: Highest resources, lowest concurrency. Queries run in parallel.

MEDIUM: Less resources, higher concurrency. Queries run in parallel.

You can modify the MEDIUM service concurrency limit. See Change MEDIUM Service Concurrency Limit for more information.

LOW: Least resources, highest concurrency. Queries run serially.

Upvotes: 1

Related Questions