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.


-How to find table where statistics are locked.

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null; 

– unlock statistics
SQL> exec dbms_stats.unlock_table_stats('<schema>', '<Table>');

-- To gather statistics on a table

SQL> exec dbms_stats.gather_index_stats('<schema>', '<Table>');

--To Lock statistics 

exec dbms_stats.lock_table_stats('<schema>', '<Table>');

– shows when stats is locked the value of stattype_locked is ALL

SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = '<table_name>' and owner = '<Schema>';

Share: