Load Data Warehouse Job Fails with ORA-06512 Error

Document ID : KB000125744
Last Modified Date : 01/02/2019
Show Technical Document Details
Issue:
The Load Data Warehouse job fails with the following error: 

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ERROR ENABLING CONSTRAINT. SQLERRM : ORA-02270: no matching unique or primary key for this column-list
ORA-06512: at "DWH.CMN_ENABLE_CONSTRAINTS_SP", line 26
Cause:
The root cause is the Load Data Warehouse job had failed, so the PK were disabled by another stored procedure CMN_DISABLE_CONSTRAINTS_SP, but not reenabled back. Or if it happened after refresh, the services were not stopped and the job was running when the backup was taken.
Resolution:
Run the following SQL query to re-enable the constraints back on the Data Warehouse database:
 
BEGIN
-- Enable PK constraints and log the exceptions into the dwh_fk_pk_exceptions table
FOR I IN (SELECT TABLE_NAME, CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE STATUS = 'DISABLED'
AND CONSTRAINT_TYPE = 'P'
)
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' ||I.TABLE_NAME|| ' ENABLE CONSTRAINT ' ||I.CONSTRAINT_NAME || ' EXCEPTIONS INTO DWH_FK_PK_EXCEPTIONS';
END LOOP I;
END;
 
Once done, re-run the Load Data Warehouse job - Full Load.
Additional Information:
KB000109473: PPM Patch installation fails with error ORA-02270 on PPM_DWH.CMN_ENABLE_DISABLE_SP

For more details and additional discussion check our Communities article:
https://communities.ca.com/thread/241780360-tech-tip-ora-02270-no-matching-unique-or-primary-key-for-this-column-list-ora-06512-at-ppmdwhcmnenabledisablesp