How to lock/unlock statistics on a table

Sometimes you may not want to analyze a table automatically and want it to analyze later manually where you are thinking data in the table does not change. In that case you can lock the statistics gathering for particular table.

Example:

SQL>conn scott/password

-- create table

SQL> create table employee ( id number, name varchar2(50) );

Table created.

-- create index

SQL> create index pk_employee_idx on employee(id);

Index created.

SQL> conn / as sysdba

-- Check the locked status of the table

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'EMPLOYEE' and owner = 'SCOTT';

STATTYPE_LOCKED

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

Null means the table is not locked for gathering statistics.

-- lock Gathering Statistics

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

PL/SQL procedure successfully completed.

-- Check the status after locked

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'EMPLOYEE' and owner = 'SCOTT';

STATTYPE_LOCKED

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

ALL

The table status is locked now.

-- Let try now to gather statistics on locked table

exec dbms_stats.gather_table_stats('SCOTT', 'EMPLOYEES');

SQL> exec dbms_stats.gather_table_stats('scott', 'employee');

BEGIN dbms_stats.gather_table_stats('scott', 'employee'); END;

*

ERROR at line 1:

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

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

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

ORA-06512: at line 1

-- Lets try now to gather statistics on the index using analyze

SQL> analyze index SCOTT.pk_employee_idx compute statistics;

analyze index scott.pk_employee_idx compute statistics

*

ERROR at line 1:

ORA-38029: object statistics are locked

-- unlock gathering statistics

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

PL/SQL procedure successfully completed.

-- Now try to gather table statistics again

SQL> exec dbms_stats.gather_table_stats('scott', 'employee');

PL/SQL procedure successfully completed.