How to lock/unlock statistics on a table

In certain cases you may want to lock statistics in a table in certain cases, for example if you want a table not be analyzed by automatic statistics job but analyze it later or in cases where you want prevent from analyzing statistics in cases where data in the table doesn’t change.
The following example shows how to lock table statistics and what happens when one tries to gather statistics on table that has statistics locked.

STEP 1: Create Table for test

SQL> create table test ( x number );

Table created.

STEP2: Create index on table.

SQL> create index test_idx on test(x);

Index created.

STEP3: Check stats are not locked on table.

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
—–

STEP4: Lock the statistics

SQL> exec dbms_stats.lock_table_stats('scott', 'test');

PL/SQL procedure successfully completed.

STEP5: Now check stats are locked status

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

STEP 6: Try to gather statistics on locked table and index


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

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

STEP7: Unlock the statistics

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

PL/SQL procedure successfully completed.

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!