Friday, 3 June 2016

Issue with Informatica Loading into Partitioned Oracle Target Table

"ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired" when Metadata Manager session fails


Problem Description

I m facing a issue in regard to loading into Partitioned Oracle Target table.
 
We have 2 sessions having same table in Oracle as Target
     a. INSERT data into Partition1
     b. UPDATE data in Partition2
 
We are trying to achieve parallelism in the workflow, and there are more Partitions and sessions to be created
 for different data but into same table, but different partitions..
 
Currently when we run both these sessions parallely, the Update session runs successfully, but the INSERT 
session gets a NOWAIT error.
NOTE: both are loading data for different partitions.

Cause
The session is running in Bulk mode to target. Bulk mode requires full table lock and at times this leads to an issue.
This is an Oracle returned error. It implies, Oracle cannot handle the bulk mode on that target table at that point of time.
Solution
To resolve this issue, turn off the Bulk mode. Edit the concerned session in Workflow Manager tool and
 make the session to run in normal mode: edit the session > Mapping tab > Target object > Target load type and change the value to Normal. 
Save the session.

No comments:

Post a Comment