Locking and Unlocking Table Statistics

DBMS_STATS.LOCK_TABLE_STATS allows you to lock a table’s statistics. This stops the table being analyzed, even with an old ANALYZE command. DBMS_STATS.UNLOCK_TABLE_STATS has the reverse effect and allows you to analyze the table again:

 

SQL> exec dbms_stats.lock_table_stats -

> (ownname => 'ANDREW', tabname => 'LOCK_TEST');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname => 'ANDREW', tabname => 'LOCK_TEST');

BEGIN dbms_stats.gather_table_stats  (ownname => 'ANDREW', tabname => 'LOCK_TEST'); END;

 

*

ERROR at line 1:

ORA-20005: object statistics are locked (stattype

= ALL)

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

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

ORA-06512: at line 1

 

SQL> analyze table andrew.lock_test

  2  compute statistics

  3  /

analyze table andrew.lock_test

*

ERROR at line 1:

ORA-38029: object statistics are locked

 

SQL> exec dbms_stats.unlock_table_stats -

> (ownname => 'ANDREW', tabname => 'LOCK_TEST');

 

PL/SQL procedure successfully completed.

 

SQL> exec dbms_stats.gather_table_stats -

> (ownname => 'ANDREW', tabname => 'LOCK_TEST');

 

PL/SQL procedure successfully completed.

 

SQL>