ORA-20005: Object Statistics Are Locked (Stattype = ALL)

If stats are locked for a  table or schema, then gathering stats will be fail with ORA-20005 error. Unlock stats and run gather stats.

SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='TEST' and owner='LEARNOMATE';

OWNER TABLE_NAME STATTYPE_LOCKED

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

LEARNOMATE TEST ALL

Unlock stats:

SQL> SQL> EXEC DBMS_STATS.unlock_table_stats('LEARNOMATE','TEST');

PL/SQL procedure successfully completed.

SQL> SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='TEST' and owner='LEARNOMATE';

OWNER TABLE_NAME STATTYPE_LOCKED

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

LEARNOMATE TEST

Now try to run stats again:

SQL> BEGIN

2 DBMS_STATS.GATHER_TABLE_STATS (

3 ownname => 'LEARNOMATE',

4 tabname => 'TEST',

5 cascade => true, ---- For collecting stats for respective indexes

6 method_opt=>'for all indexed columns size 1',

7 granularity => 'ALL',

8 estimate_percent =>dbms_stats.auto_sample_size,

9 degree => 8);

10 END;

11 /

PL/SQL procedure successfully completed.

Similarly we can unlock stats for a schema also.

SQL> EXEC DBMS_STATS.unlock_schema_stats('LEARNOMATE');

PL/SQL procedure successfully completed.