Re: How to debug resource busy error

Hello Pap,

To diagnose your issue ASH should do the trick.

Here is an example(script dash_wait_chains) : https://tanelpoder.com/2013/11/06/diagnosing-buffer-busy-waits-with-the-ash_wait_chains-sql-script-v0-2/

Thank you.

În lun., 2 mai 2022 la 05:58, Pap <oracle.developer35_at_gmail.com> a scris:

> 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-l