DWH job fails with ORA-00600 : internal error code, arguments: [16515], [D], [xx], [xxxxxxxxx]

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Load Data Warehouse (DWH) Full Load or Incremental Load fails consistently with the following error:

Oracle? - An error occurred executing this job entry : 
Couldn't execute SQL: BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE';
	DWH_CFG_PRE_CONFIG_SP(
        P_DBLINK => 'PPMDBLINK',
	    P_FULL_RELOAD => 'Y');
END;

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00600: internal error code, arguments: [16515], [D], [40], [71999148], [4], [5], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_STATS", line 1704
ORA-06512: at "SYS.DBMS_STATS", line 14485
ORA-06512: at "SYS.DBMS_STATS", line 14615
ORA-06512: at "SYS.DBMS_STATS", line 35378
ORA-06512: at "SYS.DBMS_STATS", line 36230
ORA-06512: at "SYS.DBMS_STATS", line 36716
ORA-06512: at "PPM_DWH.CMN_GATHER_TABLE_STATS_SP", line 16
ORA-06512: at "PPM_DWH.DWH_CFG_PRE_CONFIG_SP", line 13
ORA-06512: at line 3

Cause

Internal Error code 16515 refers to corrupted histogram statistics which is causing the error. You need to identify the table which has the corrupted statistics and remove them.

Environment

Release : All Supported

Component : CLARITY DATA WAREHOUSE

Resolution

1. Get the table and owning schema of the object which has corrupted statistics. Run the below command as a DBA.
   
   select owner, object_name, object_type 
from sys.dba_objects 
where object_id = 71999148;  (Note this object_ID is the 4th argument in the ORA-00600 error. In the above example it's arguments: [16515], [D], [40], [71999148]).
   
   
2. Once the information is obtained, run the below command to delete the statistics. Statistics will be re-calculated when the job is run.
   
   exec dbms_stats.delete_table_stats(ownname=> '<Table Owner>', tabname=> '<Table Name>');
   
   
3. Re-Run the DWH Full load and validate.

Additional Information

Reference also:

• Load DWH job failures - troubleshooting
• Data Warehouse (DWH) known issues
• Load DWH Full - When to run to prevent failures