Unable to drop partition due to "ORA-02266: unique/primary keys in table referenced by enabled foreign keys" error

Products

• CA Automic Workload Automation - Automation Engine

Issue/Introduction

Dropping the ILM partition is not successful due to an "ORA-02266: unique/primary keys in table referenced by enabled foreign keys" error. 

 

Environment

Release: AUTWAB99000-12.0-Automic Workload Automation-Base Edition
Component:

Resolution

First of all, check for the existing constrains in the database by executing the query: 
 

select * from all_constraints 
where owner='UC4' and r_constraint_name like '%AJPPO%'; 

 

Before doing the steps provided below, please contact Automic Support with the result you get from the SQL query provided above.

Note: If you see the errors like this: U00029125 UCUDB: Terminated the database connection because the user has been changed. Connection will be reestablished with user "username", make sure the user with appropriate permissions is executing ILM.
 

1) User must be set within the uc-ilm-settings to UPPERCASE (one of the requirements)
2) Execute "select * from all_constraints where constraint_type = 'R'; Make sure to check that all index are owned by user "USER" to find out of of the constraints
3) Make sure all index owner are correct - update istmt and set the failed record to 'O' (Open) commit
4) Make sure all wrong index (foreign keys/...) are corrected and disabled by the DBA.
5) Enable ILM again
6) Enable the wrong constraints again by the DBA.
7) If all owner are set correctly than with the next drop the problem will not occur anymore.