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

    今天在做10.2.0.4資料庫伺服器上IMP的時候，由於特殊原因，需要先匯入dmp1的表結構，然後在imp dmp2的資料，所以在imp的時候遇到一個問題：
    ORA-20005: object statistics are locked (stattype = ALL)
    操作步驟如下：
1. 匯出ecc_view使用者，生成ecc_view.dmp檔案
[oracle@rac1 ~]$ echo $NLS_LANG
american_america.ZHS16GBK
[oracle@rac1 ~]$ exp ecc_view/ecc@devdb1 file=./ecc_view.dmp
Export: Release 10.2.0.4.0 - Production on Wed Jun 24 09:42:04 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
。。。。
. about to export ECC_VIEW's tables via Conventional Path ...
. . exporting table                      ECC_VIEW1         23 rows exported
. . exporting table                              T     100000 rows exported
. . exporting table                         TABLE1          0 rows exported
. . exporting table                           TEST          4 rows exported
. exporting synonyms
. exporting views
。。。。
Export terminated successfully without warnings.

2. 建立使用者ecc_view3
 
   create user ecc_view3
   identified by ecc
   default tablespace DATA03

   grant connect, resource to ecc_view3

3. 只導表結構
[oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view.dmp rows=n FROMUSER=ECC_VIEW TOUSER=ECC_VIEW3
Import: Release 10.2.0.4.0 - Production on Wed Jun 24 09:46:31 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ECC_VIEW, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Import terminated successfully without warnings.

4. 匯入資料
a.  statistics = always(default)
   (Always imports database optimizer statistics regardless of whether or not they are questionable)
   備註：無論統計資訊是否有疑問，即是否為最新的，總是匯入resource資料庫中物件的統計資訊

[oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view.dmp ignore=y FROMUSER=ECC_VIEW TOUSER=ECC_VIEW3
Import: Release 10.2.0.4.0 - Production on Wed Jun 24 09:48:02 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ECC_VIEW, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                    "ECC_VIEW1"         23 rows imported
IMP-00017: following statement failed with ORACLE error 20005:
 "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"INX_CREATEDATE"',NULL,NULL,NULL,23"
 ",1,23,1,1,1,0,6); END;"
IMP-00003: ORACLE error 20005 encountered
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 1610
ORA-06512: at "SYS.DBMS_STATS", line 2120
ORA-06512: at "SYS.DBMS_STATS", line 5473
ORA-06512: at line 1
IMP-00017: following statement failed with ORACLE error 20005:
 "BEGIN  DBMS_STATS.SET_TABLE_STATS(NULL,'"ECC_VIEW1"',NULL,NULL,NULL,23,4,10"
 "0,6); END;"
IMP-00003: ORACLE error 20005 encountered
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 1610
ORA-06512: at "SYS.DBMS_STATS", line 2120
ORA-06512: at "SYS.DBMS_STATS", line 6055
ORA-06512: at line 1
Import terminated successfully with warnings.

重建使用者
b. statistics = none
 (Does not import or recalculate the database optimizer statistics. )
  不匯入或者重新計算資料庫中物件的統計資訊

[oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view.dmp ignore=y FROMUSER=ECC_VIEW TOUSER=ECC_VIEW3 statistics=none
Import: Release 10.2.0.4.0 - Production on Wed Jun 24 09:51:50 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ECC_VIEW, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                    "ECC_VIEW1"         23 rows imported
. . importing table                            "T"     100000 rows imported
. . importing table                       "TABLE1"          0 rows imported
. . importing table                         "TEST"          4 rows imported
Import terminated successfully without warnings.

此時不會報錯，原因為imp時，不匯入或者重新計算資料庫中物件的統計資訊，所以不會發生object statistics are locked的情況，但是此時的物件統計資訊來源於rows=n匯入時的記錄，為resource database objects的統計資訊。

下面兩個實驗的結果和第一個statistics=always的結果是一致的,因為他們都會重新計算物件的統計資訊，所以會發生object statistics are locked的情況。

c.statistics=SAFE
  (Imports database optimizer statistics back only if they are not questionable.                    
   If they are questionable, recalculates the optimizer statistics. )
   備註：當原資料exp的統計資訊和真實的統計資訊一致的時候，imp源統計資訊；
         當原資料exp的統計資訊和真實的統計資訊不一致的時候，重新計算最佳化器統計資訊

[oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view.dmp ignore=y FROMUSER=ECC_VIEW TOUSER=ECC_VIEW3 statistics=SAFE;
. . importing table                            "T"     100000 rows imported
. . importing table                       "TABLE1"          0 rows imported
. . importing table                         "TEST"          4 rows imported
IMP-00017: following statement failed with ORACLE error 38029:
 "ANALYZE  TABLE "ECC_VIEW1"  ESTIMATE STATISTICS "
IMP-00003: ORACLE error 38029 encountered
ORA-38029: object statistics are locked
IMP-00017: following statement failed with ORACLE error 38029:
 "ANALYZE  TABLE "T"  ESTIMATE STATISTICS "
IMP-00003: ORACLE error 38029 encountered
ORA-38029: object statistics are locked
Import terminated successfully with warnings.

d.statistics=RECALCULATE
 (Does not import the database optimizer statistics. Instead,recalculates them on import.  )
 不匯入源資料庫最佳化統計資訊，imp時重新計算統計資訊

[oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view.dmp ignore=y FROMUSER=ECC_VIEW TOUSER=ECC_VIEW3 statistics=RECALCULATE
 . . importing table                            "T"     100000 rows imported
. . importing table                       "TABLE1"          0 rows imported
. . importing table                         "TEST"          4 rows imported
IMP-00017: following statement failed with ORACLE error 38029:
 "ANALYZE  TABLE "ECC_VIEW1"  ESTIMATE STATISTICS "
IMP-00003: ORACLE error 38029 encountered
ORA-38029: object statistics are locked
IMP-00017: following statement failed with ORACLE error 38029:
 "ANALYZE  TABLE "T"  ESTIMATE STATISTICS "
IMP-00003: ORACLE error 38029 encountered
ORA-38029: object statistics are locked
IMP-00017: following statement failed with ORACLE error 38029:
 "ANALYZE  TABLE "TABLE1"  ESTIMATE STATISTICS "
IMP-00003: ORACLE error 38029 encountered
ORA-38029: object statistics are locked
IMP-00017: following statement failed with ORACLE error 38029:
 "ANALYZE  TABLE "TEST"  ESTIMATE STATISTICS "
IMP-00003: ORACLE error 38029 encountered
ORA-38029: object statistics are locked
Import terminated successfully with warnings.

這個是在rows=n的時候選擇預設值statistics = always時造成的問題，
我們可以選擇在第一次只匯入表結構的時候不匯入統計資訊，然後在匯入完重新收集統計資訊。

重新開始試驗部分
a. imp表結構，並且不匯入物件的統計資訊

[oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view.dmp rows=n FROMUSER=ECC_VIEW TOUSER=ECC_VIEW3 statistics=none
Import: Release 10.2.0.4.0 - Production on Wed Jun 24 10:26:22 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ECC_VIEW, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Import terminated successfully without warnings.

b. imp資料，並且重新計算物件的統計資訊

[oracle@rac1 ~]$ imp ecc_view3/ecc@devdb1 file=./ecc_view.dmp ignore=y FROMUSER=ECC_VIEW TOUSER=ECC_VIEW3 statistics=SAFE;
Import: Release 10.2.0.4.0 - Production on Wed Jun 24 10:27:51 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by ECC_VIEW, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                    "ECC_VIEW1"         23 rows imported
. . importing table                            "T"     100000 rows imported
. . importing table                       "TABLE1"          0 rows imported
. . importing table                         "TEST"          4 rows imported
Import terminated successfully without warnings.
 檢查統計資訊
 select  table_name, tablespace_name, status, num_rows, blocks,last_analyzed
 from user_tables
 table_name   tablespace_name      status   num_rows  blocks  last_analyzed
 ----------------   ---------------------------  ----------      ------------   --------  --------------------
ECC_VIEW1                        USERS        VALID                 23       5    2009-6-24 10:27:55
                     T                       USERS         VALID        100000    250    2009-6-24 10:27:55
         TABLE1                       DATA01        VALID                   0    0    2009-6-24 10:27:55
             TEST                        USERS        VALID                   4    5    2009-6-24 10:27:55

相關資料 from metalink
Symptoms
---------
Either of the following two error messages are signaled:
1. ORA-38029: object statistics are locked
2. ORA-20005: object statistics are locked (stattype = ALL)

Cause
---------
Possible Cause 1:
DBMS_STATS.LOCK_[SCHEMA|TABLE]_STATS has been used to lock statistics on the table.

Possible Cause 2:
Using import (imp) or data pump import (impdp) to import a table without data results in the table's statistics being locked in 10gR2.

Possible Cause 3: （這是我們遇到的情況）
After an IMPORT is finished for which ROWS=N, the statistics for all tables imported will be locked.
Part Number B14233-04 Database Readme 10g Release 2 (10.2) (39.5 Original Export/Import)

Possible Cause 4: If the table is a queue table then the statistics are intended to be empty and locked so that dynamic sampling will be used due to the table's volatility. During an upgrade to 10gR2 statistics on queue tables are deleted and then locked. In 10gR2 when a queue table is created statistics are locked while still empty.

Solution
---------
If the table is a queue table then the statistics should remain empty and locked so that dynamic sampling is used due to the volatility of queue tables. If the table is not a queue table, unlock the statistics using DBMS_STATS.UNLOCK_[SCHEMA|TABLE]_STATS or gather statistics on the table using DBMS_STATS.GATHER_[SCHEMA|TABLE|INDEX]_STATS and the force=>true parameter.

To prevent import (imp) from locking the table's statistics when importing a table without the rows (rows=n), use statistics=none. To prevent data pump import (impdp) from locking the table's statistics when importing a table without the rows (content=metadata_only), use exclude=(table_statistics,index_statistics).

有關imp時的引數statistics=always, none, safe, recaculate見連結:
http://blog.csdn.net/llmmysun/archive/2004/12/09/210805.aspx

參考文獻：
1.Subject:  ORA-38029 "Object Statistics Are Locked" - Possible Causes
  Doc ID:  433240.1 Type:  PROBLEM
  Modified Date :  11-JUN-2008 Status:  PUBLISHED

2. http://blog.csdn.net/llmmysun/archive/2004/12/09/210805.aspx