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>’;

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s