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

book

Article ID: 132073

calendar_today

Updated On:

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
and 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")