Error appears on DBMS_WM.REMOVEWORKSPACE or DBMS_WM.ROLLBACKWORKSPACE call.
In workspace we had modified two tables T1 and T2, T1 has forein key on T2.
We get workspace merged to LIVE, but when we want to remove workspace:
BA_PostTransaction' failed due to: Stored procedure invocation error. Error while trying to prepare and execute the BPEL_USER.AR_PR_MNG.POST_TRX API. An error occurred while preparing and executing the BPEL_USER.AR_PR_MNG.POST_TRX API. Cause: java.sql.SQLException: ORA-20005: integrity constraint (AR.NLN_PNO_FK) violated - child record found ORA-06512: at "WMSYS.WM_ERROR", line 342 ORA-06512: at "WMSYS.WM_ERROR", line 368 ORA-06512: at "AR.LT_AD_41", line 27 ORA-04088: error during execution of trigger 'AR.LT_AD_41' ORA-06512: at "WMSYS.LT", line 4962 ORA-06512: at "AR.PR_MNG", line 118 ORA-06512: at "BPEL_USER.AR_PR_MNG", line 15 ORA-06512: at line 1
It looks like bug in WM, it tries to delete parent record before child record.
Now worskpace is stacked, it can't be remowed and any ddl operations with envolved table can't be done.
Does any way exist to avoid this situation in future?
This might be a bug, but can't say for sure without more information. For example, what is the exact data being modified and then merged in the workspace? If you have an example that demonstrates the problem, then let me know. it's possible that this issue has already been reported and fixed, since the 10g version has been around for a while.