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');