Re: How to debug resource busy error

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