Hi,
Sometimes You can get “ORA-38029: object statistics are locked ” error.
Details of error are as follows.
ORA-38029: object statistics are locked
Cause: An attept was made to modify optimizer statistics of the object.
Action: Unlock statistics with the DBMS_STATS.UNLOCK_TABLE_STATS procedure on base table(s). Retry the operation if it is okay to update statistics.
ORA-38029: object statistics are locked
ORA-20005: object statistics are locked (stattype = ALL)
ORA-20005: object statistics are locked (stattype = ALL)
SQL> analyze index mehmet.salih_idx compute statistics; analyze index mehmet.salih_idx compute statistics * ERROR at line 1: ORA-38029: object statistics are locked
FirstlyUnlock table statistics as follows.
exec dbms_stats.unlock_table_stats('SCHEMA_NAME', 'TABLE_NAME');
SQL> exec dbms_stats.unlock_table_stats('MEHMET', 'SALIH');
PL/SQL procedure successfully completed.
You can now gather table stats and analyze table again.
SQL> exec dbms_stats.gather_table_stats('MEHMET', 'SALIH');
PL/SQL procedure successfully completed.
You can list all locked tables as follows,
select * from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM','DBSNMP');
And you can use the following script to generate all locked tables’ unlock scripts.
select 'exec DBMS_STATS.UNLOCK_TABLE_STATS('''||owner||''','''||table_name||''');' from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM','DBSNMP');
You can read the following post, if you don’t know what is the Database stats and how to gather table stats.