Since the upgrade to Oracle 12 or 19C the time slicing job fails continuosly with an ORA-01752 error message. What can be the reason?

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Database has been migrated from Oracle 11 to Oracle 12 and the time slicing job fails since. What could be the reason?

Log file shows following errors:

ERROR 2018-06-16 18:45:32,699 [Dispatch Time Slicing : bg@<server> (tenant=clarity)] niku.blobcrack (clarity:<user>:<session>:Time Slicing) Exception during blobcrack process/r/ncom.niku.union.persistence.PersistenceException: 
SQL error code: 1752 
Error message: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01752: cannot delete from view without exactly one key-preserved table 
ORA-06512: at line 1 
ORA-06512: at "CLARITY_USER.PRJ_BLB_SLICE_MERGE_SP", line 125 
ORA-06512: at line 1 

Executed: 
{call PRJ_BLB_SLICE_MERGE_SP(?) } 
Derived from statement: 
<?xml version="1.0" encoding="UTF-8"?> 
<statement batchable="true" sortColumnPath="/data/header/sortInfo/@sortColumn" sortDirectionPath="/data/header/sortInfo/@sortDirection" slicePath="/data/header/pagination/sliceInfo/@slice" sliceSizePath="/data/header/pagination/sliceInfo/@sliceSize" inputSource="map" xmlns="http://schemas.niku.com/2002/pmd%22%3E 
<sql dbVendor="all"> 
<text> 
{call PRJ_BLB_SLICE_MERGE_SP(?) } 
</text> 
<param name="tableName" type="string" direction="IN" expressionListDelimiter=","/> 
</sql> 
</statement>

Environment

Release: CODFSS99000-15.4.1-PPM SAAS FedRAMP-Sandbox-Small Environment
Component:

Resolution

The issue was caused by an Oracle parameter:

Oracle parameter: 

If "_complex_view_merging"= false 
Time slicing job fails. 

If "_complex_view_merging"= true 
Time slicing job succeeds 

Other things to check if job fails after setting up the parameters are if the below temporary tables have the default indexes as well.

TEMP_FISCAL_BLB_SLICES

TEMP_BLB_PROCESSED

TEMP_BLB_SLICES