Break Detection Process Failing With ORA-12838: cannot read/modify an object after modifying it in parallel Errors (Doc ID 2812323.1)

Last updated on MAY 10, 2023

Applies to:

Symptoms

On FTP 8.1.1.1 upgraded from 7.3, the Break Detection Process is throwing ORA-12838 errors:

ERROR

PARALLEL Query is Enabled
PARALLEL DML is Enabled
session date time format before alter is DD-MON-RR
session date time format after alter is DD-MON-YY
Break Detection Process Started for Process ID :218601
Validating Input Parameters :
AS OF DATE: 28-JUN-21
PREV AS OF DATE: 27-JUN-21
Processing table FSI_D_LOAN_COMMITMENTS for Break Detection
Update query for next_reprice_Date, last_reprice_date for table FSI_D_LOAN_COMMITMENTS is prepared
Update query for next_payment_date, last_payment_date for table FSI_D_LOAN_COMMITMENTS is prepared
Update query for maturity_date for table FSI_D_LOAN_COMMITMENTS is prepared
Deleting Full Break records from FSI_D_BREAK_FUNDING_CHARGES table for AS_OF_DATE: 28-JUN-21
Deleted #0 Full-Breaks Type-I records from:FSI_D_BREAK_FUNDING_CHARGES
Detecting Full-Break for FSI_D_LOAN_COMMITMENTS where instrument exists for 27-JUN-21 and not exists in 28-JUN-21
Error Occured: ORA-12838: cannot read/modify an object after modifying it in parallel

This appears to be because of the following rows in FSI_PARALLEL_PARAMETERS:

PROCESS_ENGINE_CD PARALLEL_QUERY PARALLEL_DML PARALLEL_DEGREE PARALLEL_DEGREE_POLICY
0 ENABLE ENABLE 16 MANUAL
3 ENABLE DISABLE 16 MANUAL

There are two entries for this table in version 8.1+, by comparing version 7.3+ in production there is only one record:

PROCESS_ENGINE_CD PARALLEL_QUERY PARALLEL_DML PARALLEL_DEGREE PARALLEL_DEGREE_POLICY
0 ENABLE ENABLE 16 MANUAL

Post removal of the second entry in 8111 instance, batch completed successfully. All other FTP processes are running without issue.

Changes

 Upgraded instance from 7.3.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.