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

_____________________________________________________________________________________________________________________

Problem Description:

Dbms_stats.gather_table_stats, analyze table command failed with following error.

 

BEGIN dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => ‘TRANSACTION_ID’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE,method_opt => ' FOR ALL COLUMNS SIZE AUTO', degree => 8); 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

 

SQL> ANALYZE TABLE SCOTT.LOYALTY_TRANSACTION_ID COMPUTE STATISTICS;

ANALYZE TABLE SCOTT.LOYALTY_TRANSACTION_ID COMPUTE STATISTICS

*

ERROR at line 1:

ORA-38029: object statistics are locked

 

Solution Description

The table statistics can be locked from the further update by using dbms_stats package. This will help to restrict from automatic statistics updation. When you get this error you can check whether your table is locked on further statistics updation using following query. The stattype_locked should be null to update the statistics.

SQL> select table_name, stattype_locked from dba_tab_statistics where table_name=‘TRANSACTION_ID’;

 

TABLE_NAME                     STATT

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

LOYALTY_TRANSACTION_ID         ALL

 

You can unlock the stattype_locked using the following statement.

 

SQL> exec dbms_stats.unlock_table_stats('SCOTT', ‘TRANSACTION_ID’);

 

PL/SQL procedure successfully completed.

 

SQL> select table_name, stattype_locked from dba_tab_statistics where table_name=‘TRANSACTION_ID’;

 

TABLE_NAME                     STATT

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

LOYALTY_TRANSACTION_ID          

 

Now you will be able to generate the statistics.

exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => TRANSACTION_ID’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE,method_opt => ' FOR ALL COLUMNS SIZE AUTO', degree => 8);

 

PL/SQL procedure successfully completed.

_____________________________________________________________________________________________________________________