Re: How to debug resource busy error

The last post got bounced back. So reposting...

Never thought of this scenario. But if a reference data table or parent table is referenced by two child transaction tables. And if the table-1 is doing conventional Insert , it will not let other child table-2 to disable and enable it's foreign key constraints until the commit happens. Still wanted to understand if it's legitimate lock or not? This happens even we have indexes created on the foreign keys.

Below is the details of the test case to reproduce the error.

https://gist.github.com/oracle9999/f1761cdef4850522b1dd276fa4a79bfe

On Thu, 2 Jun, 2022, 1:33 am Pap, <oracle.developer35_at_gmail.com> wrote:

> I am noway able to relate though, but I see some stats gathering
> statements updating partition objects synopses in the trace. Is it possible
> that stats gathered on the object can cause this error if we simultaneously
> fire an ALTER statement on the same object?
>
> On Wed, Jun 1, 2022 at 8:44 PM Pap <oracle.developer35_at_gmail.com> wrote:
>
>> Hello, I captured the systemstate level trace level-266 for the ORA-00054
>> error. But not able to exactly interpret the cause of the failure. Seeing
>> lot of messages with 'library object lock' in the trace file but unable to
>> pinpoint to any.
>>
>> The ALTER query which is failing with Ora-0054 looks something
>> like "ALTER TABLE XXXX ENABLE NOVALIDATE CONSTRAINT XXXXXX". Its a
>> referential constraint.
>>
>> As this system state level trace might be having some internal info, so i
>> tried redacting many of those. And replaced the actual objects names with
>> dummy ones. Sharing with limited folks here. can you please guide me , how
>> can i get to the root cause from this trace?
>>
>> I have added the trace in below location:-
>>
>> https://gist.github.com/oracle9999/d58064ab087a4a180a49336687ee437a
>>
>>
>>
>>
>> On Mon, 2 May 2022, 6:23 pm Stefan Koehler, <contact_at_soocs.de> wrote:
>>
>>> Hello Pap,
>>> I think this should be pretty easy to capture. Just set a custom event
>>> (system state dump) for ORA-00054 and then just check the trace file
>>> afterwards.
>>>
>>> SQL> alter system set events "00054 trace name systemstate level 266,
>>> lifetime 1";
>>>
>>> ... and then just wait for the next error ;-)
>>>
>>> Best Regards
>>> Stefan Koehler
>>>
>>> Independent Oracle performance consultant and researcher
>>> Website: http://www.soocs.de
>>> Twitter: _at_OracleSK
>>>
>>> > Pap <oracle.developer35_at_gmail.com> hat am 02.05.2022 04:57
>>> geschrieben:
>>> >
>>> > Hi, We are facing Ora-0054 error on specific time of the month for a
>>> job and from the line number noted in the error log its pointing to the
>>> alter statement which enables reference constraints after a direct path
>>> data load and commit. But we tried monitoringit a fewtimes during the error
>>> period/run time and surprisingly we are not seeing any lock(from
>>> v$locked_object) on the base table(say e.g. MAIN_TAB in below example) but
>>> still the job failed and itsucceeded after a coupleof rerunattempts. But it
>>> seems like , some process runs and takes lock(for may be very small time)
>>> and makes this 'ALTER' failure and we were unable to find that out.
>>> > So I wanted to understand , how to get the culprit session/sql/job so
>>> that we can have some dependency set so that this failurewon'toccur? Is
>>> there any possible trace to set which will help us to debug thisscenarioand
>>> get the details around the culprit session? Orsomehowwe can track itfrom
>>> anyof the ASH/AWR views for which we have enough retention in place?
>>> > *****The error log pointing to one of the below statements
>>> failure inside the procedure *******
>>> > ALTER TABLE SCHEMA1.MAIN_TAB ENABLE NOVALIDATE CONSTRAINT MAIN_TAB_R01;
>>> > ALTER TABLE SCHEMA1.MAIN_TAB ENABLE NOVALIDATE CONSTRAINT MAIN_TAB_R03;
>>> > ORA-00054: resource busy and acquire with NOWAIT specified or timeout
>>> expired
>>>
>>

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