How to debug resource busy error
Date: Mon, 2 May 2022 08:27:45 +0530
Message-ID: <CAEjw_fgNTJEGEvO5FEt8AWEqnEgRXV1t50w3FJ80K_GsaVDe4Q_at_mail.gmail.com>
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 monitoring it a few times 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 it succeeded after a couple of rerun attempts. 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 failure won't occur? Is there any possible trace to set which will help us to debug this scenario and get the details around the culprit session? Or somehow we can track it from any of 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
********Table structure***************
CREATE TABLE SCHEMA1.MAIN_TAB
( FFKEY NUMBER CONSTRAINT MAIN_TAB_C01 NOT NULL,
SCKEY NUMBER CONSTRAINT MAIN_TAB_C02 NOT NULL,
ACKEY NUMBER,
CKEY NUMBER CONSTRAINT MAIN_TAB_C04 NOT NULL,
CSTKEY NUMBER CONSTRAINT MAIN_TAB_C05 NOT NULL,
EPC_KEY NUMBER CONSTRAINT MAIN_TAB_C06 NOT NULL,
CRRKEY NUMBER CONSTRAINT MAIN_TAB_C07 NOT NULL
);
ALTER TABLE SCHEMA1.MAIN_TAB ADD ( CONSTRAINT MAIN_TAB_R01 FOREIGN KEY
(CKEY) REFERENCES SCHEMA1.REF_TAB1 (CKEY) ENABLE NOVALIDATE,
CONSTRAINT MAIN_TAB_R03 FOREIGN KEY (SCKEY) REFERENCES SCHEMA1.REF_TAB2
(SCKEY) ENABLE NOVALIDATE);
CREATE TABLE SCHEMA1.REF_TAB1( CKEY NUMBER CONSTRAINT REF_TAB1_C01 NOT NULL ); CREATE UNIQUE INDEX SCHEMA1.REF_TAB1_PK ON SCHEMA1.REF_TAB1(CKEY); ALTER TABLE SCHEMA1.REF_TAB1 ADD ( CONSTRAINT REF_TAB1_PK PRIMARY KEY (CKEY) USING INDEX SCHEMA1.REF_TAB1_PK ENABLE VALIDATE);
CREATE TABLE SCHEMA1.REF_TAB2( SCKEY NUMBER CONSTRAINT REF_TAB2_C01 NOT NULL ); CREATE UNIQUE INDEX SCHEMA1.REF_TAB2_PK ON SCHEMA1.REF_TAB2(SCKEY); ALTER TABLE SCHEMA1.REF_TAB2 ADD ( CONSTRAINT REF_TAB2_PK PRIMARY KEY (SCKEY) USING INDEX SCHEMA1.REF_TAB2_PK ENABLE VALIDATE);
-- http://www.freelists.org/webpage/oracle-lReceived on Mon May 02 2022 - 04:57:45 CEST