How to Fix ORA-20005: object statistics are locked (stattype = ALL)

advertisements

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
Problem Description:

Dbms_stats.gather_table_stats, analyze table command failed with following error.

 

BEGIN dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => ‘TRANSACTION_ID’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE,method_opt => ' FOR ALL COLUMNS SIZE AUTO', degree => 8); END;

 

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype = ALL)

ORA-06512: at "SYS.DBMS_STATS", line 23829

ORA-06512: at "SYS.DBMS_STATS", line 23880

ORA-06512: at line 1

 

SQL> ANALYZE TABLE SCOTT.LOYALTY_TRANSACTION_ID COMPUTE STATISTICS;

ANALYZE TABLE SCOTT.LOYALTY_TRANSACTION_ID COMPUTE STATISTICS

*

ERROR at line 1:

ORA-38029: object statistics are locked

 

Solution Description

The table statistics can be locked from the further update by using dbms_stats package. This will help to restrict from automatic statistics updation. When you get this error you can check whether your table is locked on further statistics updation using following query. The stattype_locked should be null to update the statistics.

SQL> select table_name, stattype_locked from dba_tab_statistics where table_name=‘TRANSACTION_ID’;

 

TABLE_NAME                     STATT

------------------------------ ---

LOYALTY_TRANSACTION_ID         ALL

 

You can unlock the stattype_locked using the following statement.

 

SQL> exec dbms_stats.unlock_table_stats('SCOTT', ‘TRANSACTION_ID’);

 

PL/SQL procedure successfully completed.

 

SQL> select table_name, stattype_locked from dba_tab_statistics where table_name=‘TRANSACTION_ID’;

 

TABLE_NAME                     STATT

------------------------------ ---

LOYALTY_TRANSACTION_ID          

 

Now you will be able to generate the statistics.

exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => TRANSACTION_ID’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE,method_opt => ' FOR ALL COLUMNS SIZE AUTO', degree => 8);

 

PL/SQL procedure successfully completed.

_____________________________________________________________________________________________________________________

A visitor from Vancouver viewed 'Shell Script to Run RMAN Backup in Background with' 3 mins ago
A visitor from Hubei viewed 'Fix ORA-12712: new character set must be a superse' 46 mins ago
A visitor from Wyoming viewed 'How to Force Oracle RMAN to Delete All Archive log' 2 hrs 27 mins ago
A visitor from Montreal viewed 'Purpose of ORAENV_ASK=NO / YES variable in Oracle' 2 hrs 34 mins ago
A visitor from Punta arenas viewed 'Export and Create Excel File from Oracle SQL Query' 3 hrs 31 mins ago

0 comments:

Post a Comment

Labels

Oracle (629) Script (86) General (77) Unix (47) Blog (23) Technology (19) gadget (6) games (6) Business (3) OCI (3) SQL* Loader (3) Datapump (2)
 

acehints.com Copyright 2011-25 All Rights Reserved | Site Map | Contact | Disclaimer