Skip to Main Content
  • Questions
  • Getting error -12842 : ORA-12842: CURSOR INVALIDATED DURING PARALLEL EXECUTION

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Anuj.

Asked: February 03, 2020 - 6:57 pm UTC

Answered by: Chris Saxon - Last updated: February 06, 2020 - 1:07 pm UTC

Category: Database Development - Version: Oracle Database 12.2.0.1.0 64 bit

Viewed 100+ times

You Asked

Below is my scenario. We are getting error randomly in production. when we restart the process error disappears. we are not able to reproduce even in lower environment. Can you please help in letting us know the potential issue with insert statement and how do I resolve it. I do not want to remove parallel. Is this anything to do with partitions?

t1 is partitiond on date and subpartitioned on sales_code
t2 is partitiond on date;

         
view_t is built on top of 2 tables like:         
create view view_t as select * from t1 union all select * from t2;
         

insert into t (col1,col2,col3,col4) select * from ( SELECT /*+ PARALLEL(16) */ <<columns>> from  view_t join <<multiple tables joined>> where <<conditions>> ) where <<conditions>>


Error : Getting error -12842 : ORA-12842: CURSOR INVALIDATED DURING PARALLEL EXECUTION

and we said...

The error happens because something invalidates the cursor while the query runs.

DDL on the table can cause this. Are there any partition maintenance operations (add, drop, ...) happening when you get this error?

and you rated our response

  (4 ratings)

Reviews

Are there any partition maintenance operations (add, drop, ...) happening when you get this error?

February 04, 2020 - 5:18 pm UTC

Reviewer: Anuj Gupta

No, there is no DDL partitioning operation happening.
Chris Saxon

Followup  

February 04, 2020 - 6:09 pm UTC

Is the table interval partitioned? If so, inserting values for new partitions will cause the database to issue the DDL to create a partition.

Is table interval partitioned

February 04, 2020 - 8:46 pm UTC

Reviewer: Anuj Gupta

Table in which we are inserting(In example t) the records is not partitioned at all.

Chris Saxon

Followup  

February 05, 2020 - 8:31 am UTC

What about the tables used in the view? What other activity is on those?

February 05, 2020 - 1:35 am UTC

Reviewer: A reader

Insert is on t however cursor is on view which is built on T1 & T2 both are partitioned

What about the tables used in the view? What other activity is on those?

February 06, 2020 - 10:45 am UTC

Reviewer: Anuj Gupta

Tables in the views are interval partitioned. No other activity happening on these while this insert is running.
Chris Saxon

Followup  

February 06, 2020 - 1:07 pm UTC

Something else is happening to invalidate the cursor. You'll need to investigate what's happening at the time to find out what.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.