Purge Script Provided With Patch 10256270 (Bug 10222133 ) Leaves Unreferenced Rows On Deadlock

(Doc ID 1341436.1)

Last updated on SEPTEMBER 10, 2012

Applies to:

Oracle(R) BPEL Process Manager - Version 10.1.3.4 to 10.1.3.5.0
Information in this document applies to any platform.

Symptoms

The BPEL purge script multi_threaded_looped_purge.sql provided in <patch 10256270> (i.e. <bug 10222133>) leaves unreferenced detail records in some dehydration store tables if it gets an error during execution, for example a deadlock. Unreferenced rows refer to database rows without corresponding parent rows. This script was provided as part of merge <patch 10256270> and is the official patch for <bug 10222133>.

If errors occur, such as the following deadlocks, seen in the PURGE_EXCEPTION_LOG:

1 05.05.2011 03:37:47.000000 Error code -60 : ORA-00060: deadlock detected while waiting for resource ,
Error Location = Delete from cube_scope
3 05.05.2011 03:37:51.000000 Error code -60 : ORA-00060: deadlock detected while waiting for resource ,
Error Location = Delete from cube_scope


unreferenced rows may be found iin the following tables:

AUDIT_TRAIL
DLV_SUBSCRIPTION
DOCUMENT_CI_REF
WORK_ITEM


This is very similar to what was described in <bug 10222133> and the patch for <bug 10222133> was built in order to prevent this issue from happening, but the issue is still occurring with the new script. The patch involved reordering the DELETE statements to prevent dangling rows in the case of the script being interrupted or failing.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms