Site icon IT Tutorial

ORA-38029: object statistics are locked

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)
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.
https://ittutorial.org/dbms_stats-gather-database-stats-dictionary-and-fixed-object-stats-oracle/ 

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/

 2,071 views last month,  2 views today

Exit mobile version