Wednesday, May 2, 2012

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

After database clone or datapump import, you may get the error: "ORA-20005: object statistics are locked (stattype = ALL)":
SQL> exec dbms_stats.gather_table_stats(ownname=>'cisadm',tabname=>'c0_installation',ESTIMATE_PERCENT => 30);
BEGIN dbms_stats.gather_table_stats(ownname=>'cisadm',tabname=>'c0_installation',ESTIMATE_PERCENT => 30); END;

*
ERROR at line 1:
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

You can unlock the schema stats:
exec dbms_stats.unlock_schema_stats('cisadm');

Or unlock the table stats:
exec dbms_stats.unlock_table_stats('cisadm', 'c0_installation');

And the stats can be collected:
SQL> exec dbms_stats.unlock_schema_stats('cisadm');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=>'cisadm',tabname=>'c0_installation',ESTIMATE_PERCENT => 30);

PL/SQL procedure successfully completed.

No comments:

Post a Comment