ORA-20005 WHEN GATHERING STATISTICS FOR EBS APPS 11i or R12 IN Database 10G or 11G (Doc ID 375351.1)

ORA-20005 WHEN GATHERING STATISTICS FOR EBS APPS 11i or R12 IN Database 10G or 11G (Doc ID 375351.1)

ORA-20005 WHEN GATHERING STATISTICS FOR EBS APPS 11i or R12 IN Database 10G or 11G (Doc ID 375351.1)

To Bottom

---

In this Document Symptoms Cause Solution References APPLIES TO: Oracle EBS Applications Performance - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1] Information in this document applies to any platform. 10g or 11g Database with Apps 11i or R12 SYMPTOMS Gathering statistics using concurrent jobs "Gather Schema Statistics / Gather Table Statistics" or running FND_STATS fails with "ORA-20005: object statistics are locked". Example 1: Error #1: ERROR: While GATHER_TABLE_STATS: object_name=AR.AR_REV_REC_QT***ORA-20005: object statistics are locked (stattype = ALL)***  Example 2: In GATHER_SCHEMA_STATS , schema_name= ALL percent= 10 degree = 8 internal_flag= NOBACKUP stats on table GAT_REQ_QTBL is locked 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 ('AR'); SQL> exec dbms_stats.unlock_table_stats('AR', 'AR_REV_REC_QT');