While running gather stats for a table , got below error.

 SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS (
3 ownname => ‘DBACLASS’,
4 tabname => ‘TEST2’,
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 /
BEGIN
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 34634
ORA-06512: at line 2

SOLUTION:

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='TEST2' and owner='DBACLASS';


OWNER      TABLE_NAME STATTYPE_LOCKED
---------- ---------- ----------------------------------
DBACLASS   TEST2      ALL

STATTYPE_LOCKED – ALL -> stats are locked for the table
STATTYPE_LOCKED – NULL – > stats are not locked

Unlock stats:

SQL> SQL> EXEC DBMS_STATS.unlock_table_stats('DBACLASS','TEST2');

PL/SQL procedure successfully completed.


SQL> SQL>  select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name='TEST2' and owner='DBACLASS';


OWNER      TABLE_NAME STATTYPE_LOCKED
---------- ---------- ----------------------------------
DBACLASS   TEST2

Now try to run stats again:

SQL> SQL> BEGIN
  2  DBMS_STATS.GATHER_TABLE_STATS (
  3  ownname => 'DBACLASS',
  4  tabname => 'TEST2',
  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> SQL> EXEC DBMS_STATS.unlock_schema_stats('DBACLASS');

PL/SQL procedure successfully completed.