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

zhanglei_itput發表於2009-06-23
原文網址 : http://blog.itpub.net/9252210/viewspace-607297/
Object


    在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/，如需轉載，請註明出處，否則將追究法律責任。

0
0
上一篇： RACGIMON HAS FILE HANDLE LEAK ON HEALTHCHECK FILE
下一篇： IMP ORA-20005: object statistics are locked(二)
IMP ORA-20005: object statistics are locked(一)
請登入後發表評論 登入
全部評論
zhanglei_itput

註冊時間：2009-02-10

  • 博文量
    400
  • 訪問量
    1110182

最新文章

支援我們 作者招募 使用者協議 FAQ Contact Us

北京盛拓優訊資訊科技有限公司. 版權所有  京ICP備09055130號-4  北京市公安局海淀分局網監中心備案編號：11010802021510

廣播電視節目製作經營許可證(京) 字第1234號 中國網際網路協會會員

相關文章