How to lock/unlock statistics on a table
Saturday, March 17, 2012


SQL> exec dbms_stats.lock_table_stats('scott', 'test');
PL/SQL procedure successfully completed.
– shows when stats is locked the value of stattype_locked is ALL
SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT';
STATT
—–
ALL
— try to gather statistics on locked table
SQL> exec dbms_stats.gather_index_stats('scott', 'test_idx');
BEGIN dbms_stats.gather_index_stats('scott', 'test_idx'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at “SYS.DBMS_STATS”, line 10640
ORA-06512: at “SYS.DBMS_STATS”, line 10664
ORA-06512: at line 1
– try to gather statistics on the index using analyze
SQL> analyze index ajaffer.test_idx compute statistics;
analyze index ajaffer.test_idx compute statistics
*
ERROR at line 1:
ORA-38029: object statistics are locked
– unlock statistics
SQL> exec dbms_stats.unlock_table_stats('scott', 'test');
PL/SQL procedure successfully completed.

at Saturday, March 17, 2012  

1 comments:

Unknown said... May 21, 2013 at 8:01 PM  

wonderful piece of information, I had come to know about your blog from my friend Nandu , Hyderabad, I have read at least 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that I had been looking for, I'm already your RSS reader now and I would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again, Regards, Unlock Table Oracle


Post a Comment

Subscribe to: Post Comments (Atom)
Powered by Blogger.