IMP ORA-20005: object statistics are locked(一)

    在10.2.0.1版本的数据库服务器中，dbms_stats.gather_table_stats遇到问题：
    ORA-20005: object statistics are locked (stattype = ALL)

SQL> exec dbms_stats.gather_table_stats(ownname => 'ECC_VIEW',tabname => 'TABLE_TEST1',cascade => TRUE,estimate_percent => 20);
 
begin dbms_stats.gather_table_stats(ownname => 'ECC_VIEW',tabname => 'TABLE_TEST1',cascade => TRUE,estimate_percent => 20); end;
 
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13056
ORA-06512: at "SYS.DBMS_STATS", line 13076
ORA-06512: at line 2

Symptoms
---------
While collecting statistics on queue tables, getting error ORA-20005

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SASTHAS',TABNAME => 'SRSR_QT',CASCADE => TRUE, ESTIMATE_PERCENT => 15);
END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 13182
ORA-06512: at "SYS.DBMS_STATS", line 13202
ORA-06512: at line 1

Cause
--------
This is a feature implemented in 10.2 through the fix for the unpublished bug 4244637

Solution
--------
It is not recommended to collect statistics on volatile tables like queue tables.
The fix for the bug is to lock the statistics collection on queue tables.

To avoid the ORA-20005:
- Unlock the table statistics after the import:
   execute DBMS_STATS.UNLOCK_TABLE_STATS('','

Do not import the table statistics (EXCLUDE=TABLE_STATISTICS impdp parameter)

SQL> exec dbms_stats.unlock_table_stats(ownname => 'ECC_VIEW',tabname => 'TABLE_TEST1'); 
PL/SQL procedure successfully completed 
or
SQL> EXEC DBMS_STATS.unlock_schema_stats(ownname => 'ECC_VIEW');
PL/SQL procedure successfully completed

SQL> exec dbms_stats.gather_table_stats(ownname => 'ECC_VIEW',tabname => 'TABLE_TEST1',cascade => TRUE,estimate_percent => 20); 
PL/SQL procedure successfully completed

此处为临时解决方案，真正原因见：
IMP ORA-20005: object statistics are locked(二)

参考文献：
 Subject:  ORA-20005 on Queue Table Statistics Collection
  Doc ID:  558187.1 Type:  PROBLEM
  Modified Date :  17-MAR-2008 Status:  MODERATED

Subject:  DataPump Import Without Data Locks Table Statistics
  Doc ID:  415081.1 Type:  PROBLEM
  Modified Date :  27-APR-2007 Status:  MODERATED

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9252210/viewspace-607297/，如需转载，请注明出处，否则将追究法律责任。

0

0

分享到：

上一篇： RACGIMON HAS FILE HANDLE LEAK ON HEALTHCHECK FILE

下一篇： IMP ORA-20005: object statistics are locked(二)

请登录后发表评论 登录

全部评论

<%=items[i].createtime%>

<%=items[i].content%>

<%if(items[i].items.items.length) { %>

<%for(var j=0;j

<%=items[i].items.items[j].createtime%> 回复

<%=items[i].items.items[j].username%>   回复   <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>

<%}%> <%if(items[i].items.total > 5) { %>

还有<%=items[i].items.total-5%>条评论 ) data-count=1 data-flag=true>点击查看

<%}%>

<%}%> <%}%>

zhanglei_itput

注册时间：2009-02-10

• 博文量
  400
• 访问量
  1120690

最新文章

• 概要设计与详细设计的区别
• sysdba不能远程登录
• 我们都在不断赶路忘记了出路
• iphone4S无法确定自己位置
• 我想大声告诉你
• Linux 修改IP地址和网关
• Oracle中sequence cache的测试
• pro_drop_seq
• pro_create_seq
• blog的访问量超过65000,谨此记录

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号：11010802021510

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

转载于:http://blog.itpub.net/9252210/viewspace-607297/