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