1

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’);

Apurva Tripathi
 

Apurva is a PeopleSoft consultant and a big advocate of everything PeopleSoft. He is also a technology enthusiast and loves learning and implementing newer and open source technologies. He spends his spare time updating this blog and likes to read books on self help and productivity.

Click Here to Leave a Comment Below 1 comments