ON CALL DBA SUPPORT

— Database blog

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: