How Do I Generate a Commit Based on a Condition in the Map Statement? (Doc ID 1292605.1)

Last updated on JANUARY 12, 2017

Applies to:

Oracle GoldenGate - Version: 10.0.0.0 and later   [Release: 10.0.0 and later ]
Information in this document applies to any platform.

Goal

Customer wants to truncate a partition each time a specific record appears (using an Oracle trigger) but Oracle returns an error because it cannot truncate the partition while there are I/Os pending (pending COMMIT) to complete. They need to force a COMMIT and then truncate the partition and hence continue processing.

The following is the Oracle error:

SQL Error: ORA-00054: resource busy and acquire with NOWAIT specified
00054. 00000 - "resource busy and acquire with NOWAIT specified"
*Cause: Resource interested is busy.
*Action: Retry if necessary.

The source file is a non-audited entry sequenced file, so every record in the trail has a transaction indicator (TransInd) of "X03".

Solution

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