当前位置： 首页 > backend >正文

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

backend 2025/7/5 3:52:58

    今天在做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/

http://www.xdnf.cn/news/11331.html

相关文章：

  • WAP页上传图片
  • 15个Python兼职接单平台！利用业余时间赚钱
  • 捷克论坛新ip_兰峰：提升哈尔滨冰雪文化IP打造能力
  • emule最新服务器地址,emule 国内服务器（最新emule服务器）
  • 提升C# 写入Excel操作的效率方式
  • LoadRunner压力测试方法
  • 高分卫星系列介绍及其传感器参数
  • MySQL基础入门教程（非常详细）从零基础入门到精通，看完这一篇就够了
  • 操作系统——缓冲区(buffer)与缓存(cache)
  • scrum回顾_敏捷，Scrum框架入门一篇文章就够了
  • mingw64环境搭建
  • Node.js 的常用命令介绍
  • 极域电子教室破解！
  • 深度学习网络 | GoogleNet v1-v3解析（1）
  • 192.168.1.1随身wifi登录器
  • oracle av rd ms,Oracle AWR报告生成与查看
  • 分享66个JavaGame源码总有一个是你想要的
  • 从音乐分享平台到泛音乐视频社交平台，菠萝 BOLO完成过亿元 B 轮融资
  • 黑客工具软件大全100套
  • 红米k50pro澎湃MIUI系统一键root工具德尔塔面具delta面具root教程配合已经解锁bl的设备使用
  • Windows 10 操作系统下利用USB无限网卡创建虚拟WirelessNetwork 类的封装
  • 集群技术（百科）
  • 绿色软件是怎么制作的？（转）
  • 学习vue源码（10）手写render渲染函数，在线前端开发学习
  • asp-Webshell免杀
  • Application Layer Gateway Service
  • 最新全国三级城市地区及经纬度数据下载
  • 不用微博SDK，直接使用 新浪微博 分享链接进行分享
  • tomcat7下载
  • Android 文件下载