[Ref] How to lock/unlock statistics on a table?

Ref:

http://blog.flimatech.com/2008/10/09/how-to-lockunlock-statistics-on-a-table/

– create table
SQL> create table test ( x number );

Table created.

– create index
SQL> create index test_idx on test(x);

Index created.

– shows when stats is not locked the value of stattype_locked is NULL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
– lock statistics
SQL> exec dbms_stats.lock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

– shows when stats is locked the value of stattype_locked is ALL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';

STATT
—–
ALL

— try to gather statistics on locked table
SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');
BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;

*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 10640
ORA-06512: at "SYS.DBMS_STATS", line 10664
ORA-06512: at line 1

– try to gather statistics on the index using analyze
SQL> analyze index ajaffer.test_idx compute statistics;
analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked

– unlock statistics
SQL> exec dbms_stats.unlock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.