Oracle ORA-02292 error during reorganization of table

Document ID : KB000084703
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Error Message :
U00037111 Starting reorganization for client: '0001'
U00037107 Error during reorganization of table 'AH'. Program exits with error.
U00037022 ABORTING due to error.

U00003592 UCUDB - Status: '' Native error: '2292' Msg: 'ORA-02292: integrity constraint (UC4.FK_RH_AH) violated - child record found'
U00003594 UCUDB Ret: '2' opcode: 'EXEC' SQL Stmnt: 'delete from AH where AH_idnr in (select DIVDB_PK from DIVDB)'

During the execution of the DB Unload utility the process can run into following error:
U00037107 Error during reorganization of table 'AH'. Program exits with error.
 
The related Oracle error message point to a foreign key violation on table ‘RH’.
 
Investigation

 
select ah_idnr from AH
where  AH_idnr in (select DIVDB_PK from DIVDB);
 
0 rows selected.
 
Note: if the result is ‘0 rows selected’ all records are deleted by the DB Unload utility.

If you get a list of records perform the test case below.

Test Case 

 
-- choose one of the records above
def v_ah_idnr = 1192662
select ah_idnr from AH where ah_idnr = &v_ah_idnr;
select rh_ah_idnr from rh where rh_ah_idnr = &v_ah_idnr;
select * from divdb where divdb_pk = &v_ah_idnr;
delete from RH where RH_AH_Idnr = &v_ah_idnr;
delete from AH where AH_idnr = &v_ah_idnr;

 
Results

Actual: 'ORA-02292: integrity constraint (UC4.FK_RH_AH) violated - child record found'
Expected: 1 row deleted.
 
If the Oracle error occurs again a DBA must investigate the root cause together with Automic Support (see section ‘workaround’).
The 'workaround' contains just an exception handler to delete those records which are not affected from the Oracle error.
Afterwards a DBA must investigate (may be together with Oracle customer support) why those records can't deleted from the table  'RH' even no child records exists.

The DB Unload utility will fail till this Oracle issue is fixed!
Cause:
Cause type:
Other
Root Cause: Oracle error.
Resolution:
*Cause: attempted to delete a parent key value that had a foreign dependency.
*Action: delete dependencies first then parent or disable constraint.
 
The SQL statements
To perform the DB Unload utility manually is a serious action, it should be done guided by a Automic support engineer only! Therefore, the statements itself are not posted in this article.

For further information on this knowledge base article please contact Automic Support.


Fix Status: No Fix
 
Additional Information:
Workaround :
N/A