ORA-20005: object statistics are locked

Error “ORA-20005: object statistics are locked” means that table statistics are locked and hence stats can’t be gathered on this table. Stats are locked if we don’t want stats to be gathered on some or all of our tables.

Following example explains stats locking and unlocking process.

SQL> create table test_table (id number); Table created. SQL> insert into test_table values(1); 1 row created. SQL> insert into test_table values(1); 1 row created. SQL> commit; Commit complete. -- Check if stats are current locked or unlocked. NULL returned in column STATTYPE_LOCKED column means stats are not locked SQL>  select STATTYPE_LOCKED from user_tab_statistics; STATT ----- -- Now we gather fresh stats on the table. We will be able to gather stats since stats are not locked SQL> exec dbms_stats.gather_table_stats('test','test_table'); PL/SQL procedure successfully completed. -- Now we lock the stats and try gathering stats SQL> exec dbms_stats.lock_table_stats('test','test_table'); PL/SQL procedure successfully completed. SQL>  select STATTYPE_LOCKED from user_tab_statistics; STATT ----- ALL SQL> exec dbms_stats.gather_table_stats('test','test_table'); BEGIN dbms_stats.gather_table_stats('test','test_table'); END; * ERROR at line 1: ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 23829 ORA-06512: at "SYS.DBMS_STATS", line 23880 ORA-06512: at line 1 -- Now we unlock the stats again SQL> exec dbms_stats.unlock_table_stats('test','test_table'); PL/SQL procedure successfully completed. SQL>  select STATTYPE_LOCKED from user_tab_statistics; STATT ----- SQL> exec dbms_stats.gather_table_stats('test','test_table'); PL/SQL procedure successfully completed. SQL>