ON CALL DBA SUPPORT

— Database blog

Archive for October, 2018

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

Posted by ssgottik on 04/10/2018

The table statistics can be locked from the further update / from auto agthering of stats by using dbms_stats package.  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=’EMP’;

TABLE_NAME STATT
—————————— —–
EMP ALL

You can unlock the stattype_locked using the following statement.

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

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

TABLE_NAME                     STATT

—————————— —

 

EMP

Now you will be able to generate the statistics.

Posted in SCRIPTS | Leave a Comment »