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

Document ID : KB000088402
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Dropping the ILM partition is not successful due to an "ORA-02266: unique/primary keys in table referenced by enabled foreign keys" error. 

 
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.