ORA-20005 WHEN GATHERING STATISTICS FOR EBS

Cause:
=====

This can happen with Advance Queue tables.

In 10g, if a queue is created or recreated during the upgrade, automatic statistics gather is 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 statistics gathering on those queues running the commands below.

To unlock all the tables in a schema at once:

exec dbms_stats.unlock_schema_stats('schema_owner'); 

OR

To unlock individual tables (need to run for all tables individually):

exec dbms_stats.unlock_table_stats('table_owner','table_name');

Examples:

SQL> exec dbms_stats.unlock_schema_stats ('FND');

SQL> exec dbms_stats.unlock_table_stats('FND', 'APPLSYS.FND_CP_GSM_IPC_AQTBL');