Error: "ORA-20005: object statistics are locked" Causing LOAD_PLAN_ON_DEMAND to Fail Requiring Manual Restart

(Doc ID 2369466.1)

Last updated on APRIL 19, 2018

Applies to:

Primavera Analytics Cloud Service - Version 17.12.1.0 and later
Primavera Analytics - Version 17.12.0.0 and later
Primavera Data Warehouse - Version 17.12.0.0 and later
Information in this document applies to any platform.

Symptoms

When table statistics are locked in the Data Warehouse schema, LOAD_PLAN_ON_DEMAND (when executing it's ETL steps) can fail and LOOP_ON_DEMAND_P6 generates the following message:

ERROR

ODI-1217: Session LOOP_ON_DEMAND_P6 ($session) fails with return code 7000.
ODI-1226: Step OdiWaitForChildSession fails after 1 attempt(s).
ODI-1241: Oracle Data Integrator tool execution fails.
Caused By: com.sunopsis.dwg.function.SnpsFunctionBaseException: ODI-30059: The number of child sessions in error state is greater or equal to the MAX_CHILD_ERROR parameter
at com.sunopsis.dwg.tools.WaitForChildSession.actionExecute(WaitForChildSession.java:251)
at com.sunopsis.dwg.function.SnpsFunctionBaseRepositoryConnected.execute(SnpsFunctionBaseRepositoryConnected.java:231)
at oracle.odi.runtime.agent.execution.SessionTask.execIntegratedFunction(SessionTask.java:902)
---

caused by P6_ON_DEMAND_W_RESOURCE_ASSIGNMENT_SPREAD_F package failing with:

ERROR

ODI-1226: Step Gather Table Statistics fails after 1 attempt(s).
ODI-1232: Procedure Gather Table Statistics execution fails.
ODI-1228: Task Procedure-Gather Table Statistics-ResAssignSpreadF fails on the target connection PRIMAVERA_ANALYTICS_TGT_DS.
Caused By: java.sql.SQLException: ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 2
---

BUSINESS IMPACT

The issue has the following business impact:
Due to this issue, the LOAD_PLAN_ON_DEMAND can stop running, requiring manual restart from the Analytics Administration application.

Note, if not running r17.11.2.0+ or r17.12.2.0+ the following additional issue can occur when attempting to restart the load plan from the Analytics Administration application:  Error: "Invalid Request" When Attempting To Start A Non-Running Realtime Load Plan Through The Data Warehouse Analytics Administration Web Application (Doc ID 2333715.1)

Changes

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms