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.

 

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

Unlock stats:

Now try to run stats again:

Similarly we can unlock stats for a schema also.