ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.
Load Data Warehouse Job Fails with ORA-06512 Error
Article ID: 125744
Updated On:
Products
Clarity PPM SaaS
Clarity PPM On Premise
Issue/Introduction
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
[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.
Environment
Release:
Component: ODDWH
Component: ODDWH
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;
-- 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
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
Feedback
Yes
No
Powered by
