ORA-20005: object statistics are locked

While gather stats for a index, I got below error. It is not a problem, Some reason, may be statistics collection stopped. But once you unlocked, you able to do. Please go through this workaround:

Command Executed:

execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname => 'IDX_DOJ', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 26193
ORA-06512: at line 1


SQL> col owner for a15
SQL> col TABLE_NAME for a30
SQL> col STATTYPE_LOCKED for a30
SQL> select owner,table_name,STATTYPE_LOCKED 
from dba_ind_statistics where index_name='IDX_DOJ' and owner='SCOTT';

OWNER           TABLE_NAME                STATTYPE_LOCKED
--------------- --------------------- ------------------------------
SCOTT       EMPLOYEE                       ALL

SQL>

Remember:
STATTYPE_LOCKED – ALL -> stats are locked for the table
STATTYPE_LOCKED – NULL – > stats are not locked

Now unlock the table to collect statistics:

SQL> EXEC DBMS_STATS.unlock_table_stats('SCOTT','EMPLOYEE');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_index_stats(ownname => 'SCOTT', indname => 'IDX_DOJ', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

PL/SQL procedure successfully completed.

SQL>

In case of table, you can do like below:

SQL> EXEC DBMS_STATS.unlock_table_stats('SCOTT','TEST');

PL/SQL procedure successfully completed.

SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='TEST' and owner='SCOTT';


OWNER      TABLE_NAME STATTYPE_LOCKED
---------- ---------- ----------------------------------
SCOTT    TEST

Similarly we can unlock stats for a schema also.

SQL> EXEC DBMS_STATS.unlock_schema_stats('SCOTT');

PL/SQL procedure successfully completed.