ORA-38029: object statistics are locked

What does it mean?

Statistics gathering/deletion was rejected because the optimizer statistics on a table (or schema) are locked. Locked statistics are protected from being recomputed with DBMS_STATS.

Why does it happen?

DBMS_STATS.LOCK_TABLE_STATS or LOCK_SCHEMA_STATS was called earlier (e.g. to keep statistics fixed), or a Data Pump import (especially CONTENT=METADATA_ONLY) unintentionally left the statistics locked. Running GATHER_TABLE_STATS while they are locked raises this error.

How to fix it

Unlock the statistics: EXEC DBMS_STATS.UNLOCK_TABLE_STATS('APP_SCHEMA','MY_TABLE'); or at schema level DBMS_STATS.UNLOCK_SCHEMA_STATS('APP_SCHEMA'); If you do not want to remove the lock but still want to gather, use FORCE=>TRUE: EXEC DBMS_STATS.GATHER_TABLE_STATS('APP_SCHEMA','MY_TABLE', force=>TRUE); To view the lock status: SELECT table_name, stattype_locked FROM dba_tab_statistics WHERE stattype_locked IS NOT NULL;