26 October, 2018

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

No comments:

Post a Comment

OEM Agent Installation

./agentDeploy.sh AGENT_BASE_DIR=/u001/oracle/product/agent13c OMS_HOST=oem-dev.xxx.com EM_UPLOAD_PORT=1159 AGENT_REGISTRATION_PASSWORD=*****...