Home > Database, Oracle > How to lock/unlock statistics on a table?

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>’;
Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: