ORA-20005: object statistics are locked (stattype = ALL)

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

ERROR:

FNDGSCST module: Gather Schema Statistics
+---------------------------------------------------------------------------+

Current system time is 25-NOV-2017 21:00:22

+---------------------------------------------------------------------------+

**Starts**25-NOV-2017 21:00:22
ORACLE error 20005 in FDPSTP

Cause: FDPSTP failed due to ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "APPS.FND_STATS", line 780
ORA-06512: at line 1



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:
[oracle@sujeet ~]$ sqlplus / as sysdba


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

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.