Forum Stats

  • 3,824,872 Users
  • 2,260,435 Discussions
  • 7,896,336 Comments

Discussions

Workaround for ORA-12838: cannot read/modify an object after modifying it in parallel

Hi,

I have a business use case when I have to call package which execute multiple procedures. And, if all goes well then we perform a commit. So, this is one single transaction.

Now, since the amount of data we load is high so we are using PARALLEL hint along with ENABLE_PARALLEL_DML hint in our insert statements to influence optimizer.

However, right after Inserting data we need to read from that same table. But, the restriction says - cannot read/modify an object after modifying it in parallel.

Problem- here is we cannot have commit before selecting the data, neither we are interested in removing the ENABLE_PARALLEL_DML hint.

Question- here is - Is there a way we can use ENABLE_PARALLEL_DML within single transaction.

Answers

  • mtefft
    mtefft Member Posts: 844 Gold Badge

    You can't meet your goal without refactoring your code. And it might not be possible even with refactoring. Some creativity and luck will be required.

    Does the post-insert query include only rows that have been inserted? or only rows that were already there? or both?

    What does the query produce?