11gR2: Find Source of Locked Table Statistics
Hello Friends,
When calling ANALYZE TABLE ... ESTIMATE STATISTICS, our customer is occasionally getting the following error:
ORA-38029: object statistics are locked Error executing the following SQL:
ANALYZE TABLE "CMPT_25453" ESTIMATE STATISTICS --->
Oracle.DataAccess.Client.OracleException: ORA-38029: object statistics are locked
After reviewing document 433240.1, I see that the cases where statistics are locked are fairly limited:
#1: DBMS_STATS.LOCK_[SCHEMA|TABLE]_STATS has been used to lock statistics on the table.
#2: Using import (imp) or data pump import (impdp) to import a table without data results in the table's statistics being locked in 10gR2.
#3: If the table is a queue table then the statistics are intended to be empty and locked so that dynamic sampling will be used due to the table's volatility.