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

Author: sercanbilgic

Current: Eproseed Position: Bigdata Senior Architect Past: Oracle Position: Engineered Systems Architect(Exadata,Exalogic,SuperCluster,Big Data Appliance) Past: Vodafone Position: Middleware Infrastructure Operations Senior Specialist Past: Accenture Position: Senior Developer/Programmer

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