IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
Hi,
Sometimes You can get “ORA-38029: object statistics are locked ” error.
Details of error are as follows.
ORA-38029: object statistics are lockedCause: 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.
Do you want to learn Oracle Database for Beginners, then read the following articles.
https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/
