ORA-20005: object statistics are locked (stattype = ALL)
来源：互联网 发布：淘宝卖虚拟物品刷信誉 编辑：程序博客网 时间：2024/05/29 17:25
今天从9i中exp后imp 至10g中，因为要修改栏位长度，所以先imp 加参数rows=n ； 再imp资料 ignore=y
从DBA_TAB_STATISTICS中发现统计视图中STATTYPE=ALL了，默认情况中该字段为null，表示没有锁住信息。
运行DBMS_STATS.UNLOCK_TABLE_STATS来解锁表统计信息，就可以对表进行分析了。
注意在imp结构的时候要加上 statistics=none,默认值是always
imp资料时候要加上statistics=safe 当原数据exp的统计信息和真实的统计信息一致的时候，imp源统计信息；
当原数据exp的统计信息和真实的统计信息不一致的时候，重新计算优化器统计信息
To avoid the ORA-20005:
- Unlock the table statistics after the import:
execute DBMS_STATS.UNLOCK_TABLE_STATS('<user name>','<table name>');
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
______________________________________________________________________________________________________________________________
以下是网上收集到的一个案例，转载一下。
在做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 ~]$expecc_view/ecc@devdb1file=./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 ~]$impecc_view3/ecc@devdb1file=./ecc_view.dmp rows=n FROMUSER=ECC_VIEWTOUSER=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 ~]$impecc_view3/ecc@devdb1file=./ecc_view.dmp ignore=y FROMUSER=ECC_VIEWTOUSER=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 ~]$impecc_view3/ecc@devdb1file=./ecc_view.dmp ignore=y FROMUSER=ECC_VIEWTOUSER=ECC_VIEW3statistics=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 ~]$impecc_view3/ecc@devdb1file=./ecc_view.dmp ignore=y FROMUSER=ECC_VIEW TOUSER=ECC_VIEW3statistics=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 ~]$impecc_view3/ecc@devdb1file=./ecc_view.dmp ignore=y FROMUSER=ECC_VIEW TOUSER=ECC_VIEW3statistics=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 ~]$impecc_view3/ecc@devdb1file=./ecc_view.dmp rows=n FROMUSER=ECC_VIEW TOUSER=ECC_VIEW3statistics=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 ~]$impecc_view3/ecc@devdb1file=./ecc_view.dmp ignore=y FROMUSER=ECC_VIEW TOUSER=ECC_VIEW3statistics=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 therows (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
- ORA-20005: object statistics are locked (stattype = ALL)
- ORA-20005: object statistics are locked (stattype = ALL)-转
- 错误处理 ORA-20005: object statistics are locked (stattype = ALL)
- SAP之ORA-20005: object statistics are locked
- ORA-38029:object statistics are locked
- oracle 由于impdp 引起的表统计信息被锁 ORA-20005: object statistics are locked
- ORA-39168: Object path STATISTICS was not found.
- when execute the ASM "LOCK" prefix, are all the memory being locked or are the relevant area in the
- All of Statistics 学习笔记
- ORA-12526: TNS:listener: all appropriate instances are in restricted mode
- ORA-12526: TNS:listener: all appropriate instances are in restricted mode
- ORA-12526: TNS:listener: all appropriate instances are in restricted mode
- Connection to Auxilary using connect string failed with ORA-1252: TNS:listener: all appropriate instances are blocking new conne
- Gather Schema Statistics fails with Ora-20001
- Not All Carrots Are Orange
- 水浒：All Men Are Brothers
- You Are All Excellent 1785
- We Are All Confident Idiots
- java动态代理
- 2013计算机视觉代码合集一 .
- WebService在.NET 平台下的应用
- CODE 5: Longest Consecutive Sequence
- Sparse Index实验 .
- ORA-20005: object statistics are locked (stattype = ALL)
- AndRoid五种存储方式
- hdu 1115 Lifting the Stone 多边形的重心
- 为Git设置goagent代理
- maven使用
- atoi函数 int m_atoi(char* str)
- BASH SHELL快速入门
- 给托盘添加右击菜单--变化的背景图
- DoG算子和双边滤波处理图片产生漫画效果
