ORA-20005: object statistics are locked

Cause: Gather statistics on table that has statistics locked.

Solution: Find out the table of index and unlock the Gather statistics.


SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS ('CBS_USER','IDX_ACCOUNTS_ACC_NUM');
BEGIN DBMS_STATS.GATHER_INDEX_STATS ('CBS_USER','IDX_ACCOUNTS_ACC_NUM'); END;

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


SQL>  SELECT TABLE_OWNER,TABLE_NAME,STATTYPE_LOCKED
 FROM ALL_IND_STATISTICS
 WHERE INDEX_NAME='IDX_ACCOUNTS_ACC_NUM';  2    3

TABLE_OWNER                    TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
CBS_USER                  ACCOUNTS                       ALL

SQL> SELECT OWNER, TABLE_NAME, STATTYPE_LOCKED
FROM ALL_TAB_STATISTICS
WHERE TABLE_NAME='ACCOUNTS';  2    3

OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
CBS_USER                  ACCOUNTS                       ALL

SQL> EXEC DBMS_STATS.UNLOCK_TABLE_STATS ('CBS_USER','ACCOUNTS','ALL');

PL/SQL procedure successfully completed.

SQL>  EXEC DBMS_STATS.GATHER_INDEX_STATS ('CBS_USER','IDX_ACCOUNTS_ACC_NUM');

PL/SQL procedure successfully completed.

SQL>