ORA-20005: object statistics are locked (stattype = ALL) - Solution

After creation index as below gather index giving error

 create index VIJ.VIJ_SALES_INFO_IDX1 on VIJ.VIJ_SALES_INFO(VALUE1) ;

EXEC DBMS_STATS.gather_index_stats('VIJ', 'VIJ_SALES_INFO_IDX1');

--Error while gather_index_stats--------

Error starting at line : 5 in command -

BEGIN DBMS_STATS.gather_index_stats('VIJ', 'VIJ_SALES_INFO_IDX1'); END;

Error report -

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 18701

ORA-06512: at "SYS.DBMS_STATS", line 18724

ORA-06512: at line 1

Resolution :

Cause

SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='VIJ_SALES_INFO' and owner='VIJ';

You would get STATTYPE_LOCKED  as value - ALL

STATTYPE_LOCKED – ALL stats are locked for the table
STATTYPE_LOCKED – NULL stats are not locked

EXEC DBMS_STATS.unlock_table_stats('VIJ','VIJ_SALES_INFO');

After unlock table stats , able to do gather index stats.

EXEC DBMS_STATS.gather_index_stats('VIJ', 'VIJ_SALES_INFO_IDX1');

PL/SQL procedure successfully completed.