Friday, September 16, 2011

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>

No comments: