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

While doing an application or a PeopleTools upgrade, you do update stats at a couple of steps. Also, to support your PeopleSoft instance, you do update stats at frequent interval. What update stats can cause is an error very similar to something like this:

SQL Error. Error Position: 0  Return: 20005 – ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at “SYS.DBMS_STATS”, line 23871 ORA-06512: at “SYS.DBMS_STATS”, line 23922 ORA-06512: at line 1
RECSTATS PSTREEDEFNLANG LOW
Error: SQL execute error for %UpdateStats(PSTREEDEFNLANG)
Error: Import Record name PSTREEDEFNLANG
Ended: Wed Apr 16 11:22:19 2014
Unsuccessful completion

or

Message Reason: File: Data MoverSQL error. Stmt #: 0  Error Position: 0  Return: 20005 – ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at “SYS.DBMS_STATS”, line 23871 ORA-06512: at “SYS.DBMS_STATS”, line 23922 ORA-06512: at line 1
Failed SQL stmt: RECSTATS PS_VERTICAL_MARKET LOW (200,0)
SQL Error. Error Position: 0  Return: 20005 – ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at “SYS.DBMS_STATS”, line 23871 ORA-06512: at “SYS.DBMS_STATS”, line 23922 ORA-06512: at line 1

RECSTATS PS_VERTICAL_MARKET LOW
Error: SQL execute error for %UpdateStats(PS_VERTICAL_MARKET)
Error: Import Record name VERTICAL_MARKET
Ended: Wed Apr 16 09:50:28 2014
Unsuccessful completion

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

You may have more than one table in a schema which has object statistics locked, so your first intent should be to find all the tables which have update stats locked. Use the SQL

select table_name, stattype_locked from dba_tab_statistics where owner = '<schema>' and stattype_locked is not null;

You can unlock all tables in a particular schema using the SQL:

exec dbms_stats.unlock_schema_stats('<schema_name>');

example – exec dbms_stats.unlock_schema_stats(‘SYSADM’);

Or you can unlock a particular table stats using the sql:

SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS('<schema-name>', '<table-name>');

example – SQL> exec DBMS_STATS.UNLOCK_TABLE_STATS(‘SYSADM’, ‘PS_VERTICAL_MARKET’);