ITPub博客

首页 > Linux操作系统 > Linux操作系统 > IMP ORA-20005: object statistics are locked(一)

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

原创 Linux操作系统 作者:zhanglei_itput 时间:2009-06-23 17:23:51 0 删除 编辑


    在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('','

');
OR
- 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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-10

  • 博文量
    400
  • 访问量
    1108338