Gather Schema Statistics fails with “ORA-20005: Object Statistics Are Locked (Stattype = ALL)”
Log file shows the following error,
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
.
The SQL statement being executed at the time of the error was: and was exe
+—————————————————————————+
Start of log messages from FND_FILE
+—————————————————————————+
In GATHER_SCHEMA_STATS , schema_name= All percent= 60 degree = 24 internal_flag= NOBACKUP
stats on table AQ$_WF_CONTROL_P is locked
ORA-20005: object statistics are locked (stattype = ALL)
Note in our scenario it was the above table, the table/view name will vary.
Solution:
SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name=’AQ$_WF_CONTROL_P’;
OWNER TABLE_NAME STATTYPE_LOCKED
———- ———- ———————————-
APPLSYS AQ$_WF_CONTROL_P ALL
The table stats is locked, unlock if using the following command.
SQL> EXEC DBMS_STATS.unlock_table_stats(‘APPLSYS’,’AQ$_WF_CONTROL_P’);
PL/SQL procedure successfully completed.
Verify the status,
SQL> select owner,table_name,STATTYPE_LOCKED from dba_tab_statistics where table_name=’AQ$_WF_CONTROL_P’;
OWNER TABLE_NAME STATTYPE_LOCKED
———- ———- ———————————-
APPLSYS AQ$_WF_CONTROL_P
