ora 20005 object statistics are locked stattype all

SQL error. Function:  SQLExec
  Error Position:  0
  Return:  20005 - ORA-20005: object statistics are locked (stattype = ALL) ORA-06512: at "SYS.DBMS_STATS", line 13437 ORA-06512: at "SYS.DBMS_STATS", line 13457 ORA-06512: at line 1
  Statement:  EXECUTE SYS.DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SYSADM', TABNAME => 'SS_JAMES', ESTIMATE_PERCENT => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE , DEGREE => 4, CASCADE => TRUE)
  Original Statement:  Execute sys.dbms_stats.gather_table_stats(OwnName => 'SYSADM', TabName => 'SS_JAMES', Estimate_Percent => SYS.DBMS_STATS.AUTO_SAMPLE_SIZE , Degree => 4, Cascade => TRUE)

SQL error in Exec. (2,280) SCRTY_SJTUPD.MAIN.GBL.default.1900-01-01.UPDSTATS.OnExecute  PCPC:1535  Statement:4

Process 16318 ABENDED at Step SCRTY_SJTUPD.MAIN.UPDSTATS (PeopleCode) -- RC = 8 (108,524)

Process %s ABENDED at Step %s.%s.%s (Action %s) -- RC = %s
Application Engine Program ended with a return code of 100
AE Program: scrty_sjtupd Failed!
Wed May 20 12:15:04 CDT 2009    /prod/bin/scrty_sjtupd.sh  ERROR : psae.sh FAILED, APPL=scrty_sjtupd
Wed May 20 12:15:04 CDT 2009    (/prod/bin/alert_email.sh) BEGIN : PID = 16318, APPL = scrty_sjtupd, RETURN_CODE = 9017

To fix this error I did the following : 

 exec DBMS_STATS.UNLOCK_TABLE_STATS('SYSADM','SS_JAMES');

This unlocks the statistics on the table.    The table SS_JAMES has its statistics locked and this particular job issues the following command : exec DBMS_STATS.UNLOCK_TABLE_STATS('SYSADM','SS_JAMES');

After unlocking it.   It resolved the error.

Hope this helps .

Do let me know if it does.

Thanks,

The error apparently means you tried to analyze a table whose statistics are locked (marked not to allow the optimizer statistics to be updated).

You should always include your Oracle version in a post. This error code does not appear in the 9i documentation for dbms_stats, but it does in the 10g documentation.

Why were you trying to gather statistics on a table whose statistics you have locked so that the statistics will not be updated?

When you say analyze do you mean via analayze table command? You should really only use dbms_stats to update statistics on 10g.

Check to see if the table statistics are locked. Determine why if locked. Take action to fix statistics as necessary and mentioned in the first post.

PL/SQL procedure successfully completed.

– shows when stats is locked the value of stattype_locked is ALL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
ALL

— try to gather statistics on locked table
SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');
BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1

– try to gather statistics on the index using analyze
SQL> analyze index ajaffer.test_idx compute statistics;
analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

– unlock statistics
SQL> exec dbms_stats.unlock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

ora 20005 object statistics are locked

ora 20005 object statistics are locked stattype all