Hello everybody, just wanted to share an interesting issue I faced with the community:
Load Data Warehouse starts failing for both Full and Incremental Load
Example error message from bg-dwh.log:
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
[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
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.
select * FROM DWH_INTERNAL_MD
truncate table DWH_INTERNAL_MD
This should resolve the issue. Hope this helps -Nika
Thank you for sharing this tip with the community Nika!
Tech Tip: Load Data Warehouse fails with ORA-00955: name is already used by an existing object