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

3 comments:

  1. There are lots of information about oracle have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get to the next level in oracle. Thanks for sharing this.

    Oracle Training in Chennai | Oracle Course in Chennai | hadoop training in chennai

    ReplyDelete
  2. Excellent post!!!. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it.
    SAS Training in Chennai | SAS Course in Chennai

    ReplyDelete
  3. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete

Note: Only a member of this blog may post a comment.