Error Ora 38029 Object Statistics Are Locked
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. The following example shows how to lock table statistics and what happens when one tries to gather statistics on table that has statistics locked. -- create table SQL> create table test ( x number ); Table created. -- create index SQL> create index test_idx on test(x); Index created. -- shows when stats is not locked the value of stattype_locked is NULL SQL> SELECT stattype_locked FROM dba_tab_statistics WHERE table_name = 'TEST' and owner = 'SCOTT'; STATT --- -- lock statistics 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. Share this:Click to email (Opens in new window)Share on Facebook (Opens in new window)Click to share on LinkedIn (Opens in new window)Click to share on Twitter (Opens in new window)Click to share on Google+ (Opens in new window)Click to share on Tumblr (Opens in new window)Click to share on Pinterest (Opens in new window)Click to share on Reddit (Opens in new window)Like this:Like Loading... Posted by Amin Jaffer in Statistics, Tuning Tagged: all_tab_statistics, dba_tab_statistics, gather, lock, locked, statistics, stats, unlock, user_tab_statistics Post navigation ← Information on flashback feature in10g How to monitordatapump? → 5 thoughts on “How to lock/unlock statistics on atable?” Thanks for your great information says: June 25,
Advanced Oracle Troubleshooting (ENG) Parallel Execution Masterclass (DEU) Parallel Execution Masterclass (ENG) Kontakt (DEU) / Get In Touch (ENG) Den Kostenbasierten Oracle Optimizer Verstehen (DE... Understanding The Oracle Cost Based Optimizer (ENG... Sunday, June 21, 2009 Locked table statistics and subsequent create index Just a minor thing to consider: By default in 10g and later index statistics are generated along with an index creation (option COMPUTE STATISTICS in previous releases enabled by default), so a newly created index usually has computed statistics.10g also introduced the option to https://oraclespin.com/2008/10/09/how-to-lockunlock-statistics-on-a-table/ lock table statistics.Now if you lock statistics in 10g in later using DBMS_STATS.LOCK_TABLE_STATS or LOCK_SCHEMA_STATS and create an index on a locked table the statistics for the index will not be generated along with the CREATE INDEX command. Unfortunately there is no corresponding "FORCE" option in CREATE INDEX available to overwrite that behaviour that I'm aware of so it looks http://oracle-randolf.blogspot.com/2008/08/locked-table-statistics-and-subsequent.html like you're only left with two choices:1. Use a separate DBMS_STATS.GATHER_INDEX_STATS call with the FORCE=>true option to override the lock on the statistics2. Temporarily unlock the table statistics before creating the indexThe first option can be costly if the index is large, the second option requires additional steps to be taken, and it obviously needs to be ensured that the table statistics are not modified while they are unlocked (e.g. by the default statistics job in 10g and later).A small testcase run on 10.2.0.4 Win32 follows to demonstrate the issue. I got the same result on 11.1.0.7 Win32.SQL> SQL> drop table lock_test purge;Table dropped.SQL> SQL> create table lock_test 2 as 3 select 4 * 5 from 6 all_objects 7 where 8 rownum <= 1000;Table created.SQL> SQL> exec dbms_stats.lock_table_stats(null, 'lock_test')PL/SQL procedure successfully completed.SQL> SQL> create index lock_test_idx on lock_test (object_name) compute statistics;create index lock_test_idx on lock_test (object_name) compute statistics *ERROR at line 1:ORA-38029: object statistics are lockedSQL> SQL> create index lock_test_idx on lock_test (object_name);Index created.SQL> SQL> select num_rows, last_analyzed from user_ind_statistics where index_name = 'LOCK_TEST_IDX'; NUM_ROWS LAST_ANA---------- --------SQL> SQL
ORA-38029: object statistics are locked If this error occurs while trying to analyze a table then run following to unlock the statistics: exec http://bashokan77.blogspot.com/2013/01/ora-38029-object-statistics-are-locked.html DBMS_STATS.UNLOCK_TABLE_STATS('{owner}','{table name}'); You can see list of all locked tables by running following query: select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM'); another solution select 'exec DBMS_STATS.UNLOCK_TABLE_STATS('''||owner||''','''||table_name||''');' from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM'); Posted by Ashokan .B at 02:10 Email ThisBlogThis!Share to error ora TwitterShare to FacebookShare to Pinterest 2 comments: Nandkishor Wagh9 June 2013 at 21:41interesting piece of information, I had come to know about your web-page from my friend pramod, jaipur,i have read atleast eight posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just error ora 38029 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 million once again, Regards, Unlock Table OracleReplyDeleteRepliesAshokan .B11 June 2013 at 00:45Thank you Nandi and pramod. I am also appreciate you both.Would you please share your issues regarding which is you are facing with oracle tech.I always thanking you for your replay which is getting me some inspirations for sharing my experience with oracle.DeleteReplyAdd commentLoad more... Newer Post Older Post Home Subscribe to: Post Comments (Atom) Popular Posts oracle 11G: ORA-01194: file 1 needs more recovery to be consis... oracle 11G: ORA-01194: file 1 needs more recovery to be consis...: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data f... ALTER SYSTEM ARCHIVE LOG CURRENT is the best practice for production backup scripts with RMAN.(RMAN-06054,ORA-00279,ORA-00289) ALTER SYSTEM ARCHIVE LOG CURRENT is the best practice for production backup scripts with RMAN. alte