Oracle Database Discussions

Versions: Oracle 9.2.0.8 and Oracle 10.2.0.4

Background:

Project is working on upgrade from 9.2.0.8 to 10.2.0.4. I'm being provided information remotely to try and diagnose and resolve a sporadic locking issue with the system.

Application runs normally in dev/test on both stacks. In a performance soak test, a session occasionally receives:

"ORA-00054: resource busy and acquire with NOWAIT specified"

I'm told by an engineer that this has been seen on both platforms.

When investigating the code, a couple of possibilities are suggested as the cause. All of which relate to concurrent usage and system configuration. That is, application coding/design issues. All these leads appear to be dead ends.

The table that is being accessed when the error is encountered has now been rebuilt with a larger INITRANS value, and the error "went away".

Question:

I've never seen ORA-00054 raised due to ITL processing ( I have seen ORA-00060 ). My concern therefore is that, the INITRANS change has not solved the issue, but rather the table rebuild required to modify this value has somehow sidestepped this error - and the error will therefore be seen again, at a more inopportune time.

* Can an ORA-00054 ever be caused by ITL processing?
* What are the other possible causes ( that might be affected by a table re-build )?
* Could an unindexed foreign key cause an ORA-OOO54 in the correct circumstances? (I'm aware that the FK check algorithm changed in 10G )

Very interested in any response.

Regards,

Mathew Butler