Object Statistics Are Locked

ORA-38029 "Object Statistics Are Locked" - Possible Causes [ID 433240.1]

Cause: Using import (imp) or data pump import (impdp) to import a table without data results in the table's statistics being locked in 10gR2.

SQL> exec dbms_stats.gather_table_stats('AE1T3OBY','id_emp_leave_details',cascade => true);

BEGIN dbms_stats.gather_table_stats('AE1T3OBY','id_emp_leave_details',cascade => true); 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

This query shows that the table statistics are locked.

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

Unlock it

Sql> exec dbms_stats.unlock_schema_stats('AE1T3OBY');

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

Now gather the table statistics

SQL> exec dbms_stats.gather_table_stats('AE1T3OBY','id_emp_leave_details',cascade => TRUE);

PL/SQL procedure successfully completed.

Gathering statistics sample:

begin
dbms_stats.gather_table_stats( ownname=> 'AE1T3', tabname=> 'ID_NUMBER_GEN_SOURCE' , estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, cascade=> DBMS_STATS.AUTO_CASCADE, degree=> null, no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, granularity=> 'AUTO', method_opt=> 'FOR ALL COLUMNS SIZE AUTO');
end;