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

ORA-20005

When we tried to gather some statistics on a table 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 when 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 ORA-20005

To solve ORA-20005, we have several options.

1. Override Locked Statistics by Force

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.