Reading resource busy error system trace
Date: Sat, 11 Jun 2022 18:49:39 +0530
Message-ID: <CAEjw_fjbkAxeuT5-aU7brhni2MQB2z_vpfY+3mG_ZN6xVwCCuQ_at_mail.gmail.com>
I had posted this one in the past here. But now having the trace collected , finding it hard to understand the trace. Appreciate guidance here.
Background:-
We were encountering intermittent Ora-0054 error on specific time towards the end of the month for a job and from the line number noted in the error log it's pointing to the alter statement which enables reference constraints after a direct path data load and commit.
But we tried monitoring it a few times during the error period/run time but were not seeing any lock on the base table but still the job failed and it succeeded after a couple of rerun attempts. We tried enabling the system trace as below and did capture the trace.
I am unable to understand the culprit of this error out of this system trace. I posted the trace in the below location with a few masked obj names. It has a lot of information(~50K+ lines) but I think I am supposed to look into a certain section of it, but I am not able to find that out. Could you please guide me here?
alter system set events "00054 trace name systemstate level 266, lifetime 1";
https://gist.github.com/oracle9999/10e33916ad43748b7802719812b4ddc2
*****Below statements failing
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-lReceived on Sat Jun 11 2022 - 15:19:39 CEST