Kokei Labs

Sometimes, you may want to lock the statistics update on certian tables in order to keep the explain plan stable. When a table's statistics collection is locked, you can no longer update its statistics either automatically or manually
This example shows how to lock table statistics and what happens when one tries to gather statistics on table that has statistics locked.

--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='USER1'
STATT
-----

--lock statistics
SQL> exec dbms_stats.lock_table_stats('user1', '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 = 'USER1';
STATT
-----
ALL

--try to gather statistics on locked table
SQL> exec dbms_stats.gather_table_stats('user1', 'test');
BEGIN dbms_stats.gather_table_stats('user1', 'test'); END;

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

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

– unlock statistics
SQL> exec dbms_stats.unlock_table_stats('user1', 'test');
PL/SQL procedure successfully completed.