Sunday, June 7, 2020

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



DB : Oracle 19.5
OS : RHEL 7

When try to run stats on table the following error appears 

SQL>  execute dbms_stats.gather_table_stats(ownname => 'ADMIN', tabname =>'CUST_TBL', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZEAUTO');
BEGIN dbms_stats.gather_table_stats(ownname => 'ADMIN', tabname =>'CUST_TBL', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZEAUTO'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 40751
ORA-06512: at "SYS.DBMS_STATS", line 40035
ORA-06512: at "SYS.DBMS_STATS", line 9393
ORA-06512: at "SYS.DBMS_STATS", line 10317
ORA-06512: at "SYS.DBMS_STATS", line 39324
ORA-06512: at "SYS.DBMS_STATS", line 40183
ORA-06512: at "SYS.DBMS_STATS", line 40732
ORA-06512: at line 1


SQL> select table_name, stattype_locked,last_analyzed from dba_tab_statistics where owner='ADMIN' and table_name='CUST_TBL';

TABLE_NAME         STATT          LAST_ANAL
----------------------     ---------      ---------
CUST_TBL                 ALL     06-APR-20
CUST_TBL                          06-APR-20

Use below command to unlock the table 

SQL>  exec dbms_stats.unlock_table_stats ('ADMIN','CUST_TBL');

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.31

SQL>  select table_name, stattype_locked from dba_tab_statistics where owner='ADMIN' and table_name='CUST_TBL';

TABLE_NAME             STATT
---------------------- -----
CUST_TBL
CUST_TBL


SQL>  execute dbms_stats.gather_table_stats(ownname => 'ADMIN', tabname =>'CUST_TBL', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.00


To find all table stats locked in a database:

SQL> select owner,count(*) from dba_tab_statistics where stattype_locked is not null group by owner;

OWNER                    COUNT(*)
---------------------- ----------
SYS                            13
ADMIN                        331
SCOTT                         51
GSMADMIN_INTERNAL               1
GGOLD                        2

5 rows selected.

You can generate script to unlock all at once

select 'exec dbms_stats.unlock_table_stats  (''’ ||a.owner || ‘'',''' || a.table_name || ''')' ||’;’  from dba_tab_statistics a 
 where stattype_locked is not null and owner='<OWNER_NAME>';

0 comments:

Post a Comment

Auto Scroll Stop Scroll