ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

ISSUE:

Unable to recreate the index due to below error.


SQL> create index ICX.XX_ICX_CAT_ITEMS on ICX.ICX_CAT_ITEMS_CTX
(INVENTORY_ITEM_ID,ORG_ID,LANGUAGE,SEQUENCE) online compute statistics parallel 8 nologging   2
  3  ;
create index ICX.XX_ICX_CAT_ITEMS on ICX.ICX_CAT_ITEMS_CTX
                 *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> drop index ICX.XX_ICX_CAT_ITEMS;
drop index ICX.XX_ICX_CAT_ITEMS
               *
ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


Solution:

We have noticed that this error has occurred due to above object is being used by Some concurrent request.

And also observed that same session is shoing in BLOCKING SESSIONS.


1. Wait until that concurrent request gets completed.

OR


2. Cancel that perticular request and kill the the SID of that program.


Now try to drop that index and create it

ADOP Patching failed with JTF user lock error

Issue:

We faced JTF user lock issue while applying the patch using ADOP.

==> We checked in dba_users table and noticed that JTF user got locked and expired

==> We have unlocked and reset the password of JTF uing FNDCPASS.

==> Initiated the patch again with restart=yes

adop phase=apply restart=yes

==> But we faced JTF user lock issue again

Solution:

Give abandon=yes and initiate the patch again

adop phase=apply abandon=yes

ORA-38881: Cannot drop tablespace TABLESPACE_STG on primary database due to guaranteed restore points.

Issue:

Unable to drop a tablespace due to below error.

SQL> drop tablespace TABLESPACE_STG including contents and datafiles;

ORA-38881: Cannot drop tablespace TABLESPACE_STG on primary database due to guaranteed restore points.

Soloution:

We need to set below underscore parameter as "true"

SQL> show parameter _allow_drop_ts_with_grp

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

_allow_drop_ts_with_grp              boolean     FALSE

SQL>

FIX:

SQL> alter system set "_allow_drop_ts_with_grp"=true;

System altered.

SQL> show parameter _allow_drop_ts_with_grp

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

_allow_drop_ts_with_grp              boolean     TRUE

SQL>

SQL> drop tablespace TABLESPACE_STG including contents and datafiles;

Tablespace dropped.

Revert the Changes :
-------------------------

You can revert the underscore parameter again.

alter system set "_allow_drop_ts_with_grp"=false;

SQL> show parameter _allow_drop_ts_with_grp

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_allow_drop_ts_with_grp              boolean     FALSE
SQL>