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.

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

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

 282 views last month,  3 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply