ORA-20005的解決方法

tolywang發表於2009-11-29

 

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.3

This problem can occur on any platform.

DataPump Import without data (CONTENT=METADATA_ONLY) locks statistics.

Executing the DBMS_STATS.GATHER_TABLE_STATS to collect the statistics on the table imported, it fails with:

ORA-20005: object statistics are locked (stattype = ALL)

This is an expected behavior. in 10.2.0

The statistics are locked during a DataPump Import if export or import were performed with CONTENT = METADATA_ONLY.

This is because automatic statistics gathering is enabled by default in 10g. Therefore, the imported statistics, if not locked, are lost the next time the auto-stats job runs

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

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

OWNER TABLE_NAME STATT

------------------------------

APPLSYS WF_NOTIFICATION_OUT ALL

1 row selected.

SQL> exec dbms_stats.unlock_table_stats('APPLSYS','WF_NOTIFICATION_OUT');

PL/SQL procedure successfully completed.

SQL> select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null;

no rows selected

問題解決。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/35489/viewspace-620936/，如需轉載，請註明出處，否則將追究法律責任。

0
0
上一篇： 執行收集統計資訊dbms_stats.gather_table_stats包的bug
下一篇： Oracle 10g 安裝及單例項遷移到RAC
ORA-20005的解決方法
請登入後發表評論 登入
全部評論
tolywang
專家姓名：王琦
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技術討論，希望在這裡一起分享知識，討論技術，暢談人生 。

註冊時間：2007-12-10

  • 博文量
    5595
  • 訪問量
    13225419

最新文章

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

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

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

相關文章