SQL Performance (MOSC)

MOSC Banner

11gR2: Find Source of Locked Table Statistics

edited Oct 7, 2015 3:46PM in SQL Performance (MOSC) 4 commentsAnswered

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center