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

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

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