ora-00054 resource busy and acquire with nowait specified or timeout expired

Error:

AD Worker error:

The following ORACLE error:

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

(OR)

SQL> ALTER INDEX PJI.PJI_FP_XBS_ACCUM_F_N1 STORAGE (FREELISTS 4);

ALTER INDEX PJI.PJI_FP_XBS_ACCUM_F_N1 STORAGE (FREELISTS 4)

                *

ERROR at line 1:

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

Solution :

 SQL> select dbms_stats.get_param('NO_INVALIDATE') from dual;
DBMS_STATS.GET_PARAM('NO_INVALIDATE') 
-----------------------------------------------
FALSE

Note : Use the default setting of NO_INVALIDATE = DBMS_STATS.AUTO_INVALIDATE as this does not require locking the object.

SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS (pname=> 'NO_INVALIDATE', pvalue=> 'DBMS_STATS.AUTO_INVALIDATE');

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_param('NO_INVALIDATE') from dual;

DBMS_STATS.GET_PARAM('NO_INVALIDATE')

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

DBMS_STATS.AUTO_INVALIDATE

SQL> select PCT_FREE,INI_TRANS,MAX_TRANS from ind where index_name like '% PJI_FP_XBS_ACCUM_F_N1%’;

 SQL> show parameter ddl_lock_timeout;

 SQL> alter session set DDL_LOCK_TIMEOUT=100;

è you can alter the session or the system parameter as well.

SQL> alter session set DDL_LOCK_TIMEOUT=100;

 Session altered.

 SQL> alter system set DDL_LOCK_TIMEOUT=100 ;

 System altered.

Note : This feature may be used when running a process in a parallel mode which updates same table from more than one source.

 +++++++++++++++++++++++++++++++++++++++++