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

  • by

ORA-20005

When we tried to gather some statistics on an index which has never been analyzed, we saw ORA-20005 like this:

SQL> exec dbms_stats.gather_table_stats(ownname => 'ERPAPP', tabname => 'PAYMENT_DAY');
BEGIN dbms_stats.gather_table_stats(ownname => 'ERPAPP', tabname => 'PAYMENT_DAY'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 40751
ORA-06512: at "SYS.DBMS_STATS", line 40035
ORA-06512: at "SYS.DBMS_STATS", line 9393
ORA-06512: at "SYS.DBMS_STATS", line 10317
ORA-06512: at "SYS.DBMS_STATS", line 39324
ORA-06512: at "SYS.DBMS_STATS", line 40183
ORA-06512: at "SYS.DBMS_STATS", line 40732
ORA-06512: at line 1

ORA-20005 means that the statistics of the object is locked, we can't overwrite the frozen statistics by gathering statistics on the object.

Let's check the statistics status of the user's tables by querying ALL_TAB_STATISTICS.

SQL> select table_name, stattype_locked from all_tab_statistics where owner='ERPAPP';

TABLE_NAME                STATTYPE_LOCKED
------------------------- ---------------
PAYMENT_DAY               ALL
PAYMENT_MONTH             ALL
...

As we can see, almost all tables' statistics of this user are locked (frozen).

Solution

To solve ORA-20005, we have several options.

1. Force Override Locked Statistics

Although the statistics of the object is locked, we can overwrite it by adding a FORCE parameter.

SQL> exec dbms_stats.gather_table_stats(ownname => 'ERPAPP', tabname => 'PAYMENT_DAY', force => TRUE);

PL/SQL procedure successfully completed.

The table is analyzed.

2. Unlock Single Table

In some cases, you can unlock only one table's statistics. Subsequently, all dependent statistics are unlocked.

SQL> exec dbms_stats.unlock_table_stats('ERPAPP','PAYMENT_DAY');

PL/SQL procedure successfully completed.

Then try to analyze the table again.

3. Unlock Entire Schema

If all migrated statistics are locked, we can unlock the entire schema.

SQL> exec dbms_stats.unlock_schema_stats('ERPAPP');

PL/SQL procedure successfully completed.

Then try to analyze the table again.

Further reading: How to Gather Statistics on Database Level for Various Situations.

Leave a Reply

Your email address will not be published. Required fields are marked *