Re: Reading resource busy error system trace

Pap,

A quick write-up (in two parts) prompted by your question and use of systemstate dump:

https://jonathanlewis.wordpress.com/2022/07/06/ora-00054-pt-1/ https://jonathanlewis.wordpress.com/2022/07/08/ora-00054-pt-2/

Regards
Jonathan Lewis

On Mon, 4 Jul 2022 at 19:30, Pap <oracle.developer35_at_gmail.com> wrote:

> Thank you Jonathan.
>
> I don't have the errorstack trace with me , but I will try to set this
> 'errorstack' trace and try to capture the object_id in the next occurrence.
>
> However, till now , I had generated the 'systemstate' trace and was
> following the below Nenad's blog for identifying the blocking session of
> the Ora-0054 from the system dump. And in our case the trace was pointing
> to the same session as the blocking session and the lock as 'DML lock'
> which is odd.
>
>
> https://nenadnoveljic.com/blog/troubleshooting-ora-00054-resource-busy-and-acquire-with-nowait-specified-or-timeout-expired/
>
>
>
>
> On Mon, Jul 4, 2022 at 11:06 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
> wrote:
>
>>
>> Systemstate doesn't give you the full "Call Stack Trace", you need
>> errorstack:
>>
>> alter system set events '54 trace name errorstack level 1';
>>
>>
>> Regards
>> Jonathan Lewis
>>
>>
>> On Mon, 4 Jul 2022 at 17:06, Pap <oracle.developer35_at_gmail.com> wrote:
>>
>>> I searched with string 'Call Stack Trace' but didnt find any such in all
>>> the captured trace files generated with that ospid.
>>>
>>> I double checked with the team and found the level 266 trace was not
>>> getting generated somehow, so they did set it as level 258 as below.
>>> However wondering if this detail expanded 'call stack trace' is not
>>> captured just because it's been generated with level 258 as opposed to
>>> level 266.
>>>
>>> ALTER SYSTEM SET EVENTS '54 trace name systemstate level 258';
>>>
>>>
>>>
>>>
>>> On Mon, 4 Jul 2022, 7:52 pm Jonathan Lewis, <jlewisoracle_at_gmail.com>
>>> wrote:
>>>
>>>>
>>>> You didn't miss anything.
>>>>
>>>> I read your comments about *systemstate level 266*, but my fingers
>>>> typed *errorstack level 1* when I wasn't watching because that's a
>>>> good first choice to try when you see a session raising an error.
>>>>
>>>> This is an extract from my test dump (with a lot of lines near the
>>>> start of the call stack deleted):
>>>>
>>>>

--
http://www.freelists.org/webpage/oracle-l