Friday 11 January 2013

ORA-38029: object statistics are locked





ERROR at line 1:
ORA-38029: object statistics are locked
ORA-06512: at "SYS.DBMS_DDL", line 257
ORA-06512: at "SYS.DBMS_UTILITY", line 488
ORA-06512: at line 1


ORA-38029: object statistics are locked


If this error occurs while trying to analyze a table then run following to unlock the statistics:

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



2 comments:

  1. interesting 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 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 Oracle


    ReplyDelete
    Replies
    1. Thank 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.

      Delete