Nika_Hadzhikidi

Tech Tip: Load Data Warehouse fails with ORA-00955: name is already used by an existing object

Discussion created by Nika_Hadzhikidi Employee on Jun 20, 2017
Latest reply on Jun 20, 2017 by Chris_Hackett

Hello everybody, just wanted to share an interesting issue I faced with the community:

 

Symptom:

Load Data Warehouse starts failing for both Full and Incremental Load

 

Example error message from bg-dwh.log:

BEGIN

DWH_INV_ASSIGN_PER_FACTS_LOAD(

P_DBLINK => PPMDBLINK',

P_LAST_LOAD_DATE => to_date('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss'),

P_CURRENT_DIM_LOAD_DATE => to_date('2017/06/14 16:05:04', 'yyyy/mm/dd HH24:MI:SS'),

P_ARRAY_SIZE => 50000

);

END;

 

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_INV_ASSIGN_PERIOD_FACTS. SQLERRM : ORA-00955: name is already used by an existing object

ORA-06512: at "DWH_INV_ASSIGN_PER_FACTS_LOAD", line 61

ORA-06512: at line 2

 

Root Cause

This would happen when a record is stuck in DWH_INTERNAL_MD table and conflicts with an existing object or index. DWH_INTERNAL_MD is a metadata table used to hold the statements to recreate indexes dropped by the job once they are needed again.

 

Solution:

  1. Connect to your PPM_DWH database with a database analyse tool and ppm_dwh user
  2. Run the query:

select * FROM DWH_INTERNAL_MD

  1. Run the create statements you see in the output manually on the PPM_DWH database. Usually one of them will have an issue (the above ORA-00955 error) , then just skip this one
  2. When all the objects are created, run the following:

truncate table DWH_INTERNAL_MD

  1. Now run Load Data Warehouse job - Full Load

 

This should resolve the issue. Hope this helps -Nika

Outcomes