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:
- Connect to your PPM_DWH database with a database analyse tool and ppm_dwh user
- Run the query:
select * FROM DWH_INTERNAL_MD
- 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
- When all the objects are created, run the following:
truncate table DWH_INTERNAL_MD
- Now run Load Data Warehouse job - Full Load
This should resolve the issue. Hope this helps -Nika