Datamart Extraction Job Fails with ORA-02429: cannot drop index used for enforcement of unique/primary key

Products

• Clarity PPM On Premise

Issue/Introduction

Datamart Extraction Job (DME) fails with error
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: 
Error in NBI_EXTRACT_SP - Calling NBI_EXTR_RF_SP: ORA-20000: Error in NBI_EXTR_RF_SP - SELECTING PROJECTS COUNT: ORA-20000: 
Error in NBI_RF_SP - : ORA-02429: cannot drop index used for enforcement of unique/primary key

Environment

Release:
Component: ODENV

Resolution

On Premise customers, see steps below. On Demand/SaaS customers, please contact Broadcom support for assistance

Step 1: Check for constraints:

select * from USER_CONSTRAINTS
where CONSTRAINT_NAME like '%NBI_R_FACTS_PK%'
and status = 'ENABLED'

Step 2: If constraints are found, disable the constraint using the below query:

alter table "Schema name"."NBI_R_FACTS" disable constraint "NBI_R_FACTS_PK"; 

Step 3: Drop and recreate the below index

• drop the index NBI_R_FACTS_U1

drop index NBI_R_FACTS_U1

• recreate the index NBI_R_FACTS_U1

 CREATE UNIQUE INDEX NBI_R_FACTS_U1 ON NBI_R_FACTS ("FACT_DATE", "RESOURCE_ID")