ORA-20005: object statistics are locked

Gather stats fails after upgrade to Oracle 10G

Gather Schema Statistics or Gather Table Statistics or running FND_STATS  fails with "ORA-20005: object statistics are locked".

Example:
ERROR: While GATHER_TABLE_STATS:
object_name=AR.AR_REV_REC_QT***ORA-20005: object statistics are locked (stattype = ALL)***


This can happen with Advance Queue tables.
In 10g, if a queue is created or recreated during the upgrade, automatic statistics gather gets locked (disabled) on these queue.
The following statement can be used to check the tables which have statistics locked:

  select owner, table_name, stattype_locked
    from dba_tab_statistics
    where stattype_locked is not null; 


SOLUTION:
Unlock gather stats running on those queues :
exec dbms_stats.unlock_schema_stats('schema_owner');
exec dbms_stats.unlock_table_stats('table_owner','table_name');


Example:
SQL> exec dbms_stats.unlock_schema_stats ('AR');
SQL> exec dbms_stats.unlock_table_stats('AR', 'AR_REV_REC_QT'); 




No comments:

How to set the schema session limit in Oracle 23 AI Database.

  In Oracle, to set a session limit per schema (user)—i.e., control how many concurrent sessions a specific user can have. You use profiles ...