今天安装windows系统下的oracle数据库，版本是11.2.0.1.0在，从linux系统的11.2.0.4.0版本的数据库导出，导入至windows系统下的oracle数据库下的出现报错：

———————————————————————————————————————————————————

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_ASSOCIATIONS','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_ASSOCIATIONS',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_BODEF_PROP','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_BODEF_PROP',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_FIELD_GROUP','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_FIELD_GROUP',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_SOLUTION_ACL','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_SOLUTION_ACL',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_UDB_LOG','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_UDB_LOG',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_DISPLAY_LAYOUT','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_DISPLAY_LAYOUT',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_EMAIL_TEMPLATE','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_EMAIL_TEMPLATE',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_GLOBAL_SCRIPT','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_GLOBAL_SCRIPT',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_LOCALE_OBJECT','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_LOCALE_OBJECT',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_SOLUTION','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_SOLUTION',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_SOL_VERSION','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_SOL_VERSION',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_SOL_VERSION_XML','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_SOL_VERSION_XML',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,fla

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_STATE','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_STATE',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,n2

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_STATEFLOW','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_STATEFLOW',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_STATEFLOW_OBJECT','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_STATEFLOW_OBJECT',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,f

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_TEXT_BLOB','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_TEXT_BLOB',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_TRANSITION','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_TRANSITION',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_UDB_INFO','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_UDB_INFO',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到任何数据

失败的 sql 为:

DECLARE I_N VARCHAR2(60); I_O VARCHAR2(60); c DBMS_METADATA.T_VAR_COLL; df varchar2(21) := 'YYYY-MM-DD:HH24:MI:SS'; BEGIN DELETE FROM "SYS"."IMPDP_STATS"; c(1) := DBMS_METADATA.GET_STAT_COLNAME('HAPPYLIFE2','HS_USERDB','''''',NULL,0); DBMS_METADATA.GET_STAT_INDNAME('HAPPYLIFE2','HS_USERDB',c,1,i_o,i_n); INSERT INTO "SYS"."IMPDP_STATS" (type,version,flags,c1,c2,c3,c5,n1,

处理对象类型 SCHEMA_EXPORT/VIEW/VIEW

处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

————————————————————————————————

在网上导了这样的帖子，挺有有用的转载一下：

前几天帮助客户做了一次数据库导出，导出语句如下：

EXPDP SYSTEM/PASSWORD DIRECTORY=DUMP DUMPFILE=CRM20.DMP LOGFILE=CRM20_EXPDP.LOG SCHEMAS=CRM20

导出是成功的。

导完后丢给了客户，因为他们建了一个测试库，需要这个用户的数据。

但是，过了几天客户反映导入报错，如下：

ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误:

ORA-01403: 未找到数据

失败的 sql 为:

DECLARE IND_NAME VARCHAR2(60); IND_OWNER VARCHAR2(60); BEGIN DELETE FROM "SYS"."IMPDP_STATS"; SELECT index_name, index_owner INTO IND_NAME, IND_OWNER

FROM (SELECT UNIQUE sgc1.index_name, sgc1.index_owner,

COUNT(*) mycount

FROM sys.ku$_find_sgc_view sgc1,

TABLE (sgc1.col_list) mycol

跑过去一看，其导入的数据库版本是10.2.0.1 for windows2003 64bit，但是我导出的数据库版本是10.2.0.4 for RHEL AS 5 64bit，便轻率地下了一个结论：数据库版本差异导致了这个问题。

以为客户换个版本的数据库就完了，正常了。结果客户在10.2.0.4 for AIX5 64bit上问题依旧。

懵了！

开始认真对待---因为客户也认真了！

网上搜了几个帖子，没什么帮助。关键时刻，还得metalink出击！

于是，怀着试试看的态度，在metalink上搜了一把，收获真大了去了---BUG:6156708！

有种说不出来的喜悦，可以给客户交代了，在给客户交代之前，说下这个BUG:6156708：

#############################################################################

Applies to: Oracle Server - Enterprise Edition - Version: 10.2.0.3 to 11.1.0.6 - Release: 10.2 to 11.1

Information in this document applies to any platform.

SymptomsA DataPump import using the parameter REMAP_SCHEMA reports errors ORA-39083 and ORA-1403 while importing index statistics:

ORA-39083: Object type INDEX_STATISTICS failed to create with error:

ORA-01403: no data found

Failing sql is:

DECLARE IND_NAME VARCHAR2(60); IND_OWNER VARCHAR2(60); BEGIN DELETE FROM

"SYS"."IMPDP_STATS"; SELECT index_name, index_owner INTO IND_NAME, IND_OWNER

FROM (SELECT UNIQUE sgc1.index_name, sgc1.index_owner,

COUNT(*) mycount

FROM sys.ku$_find_sgc_view sgc1,

TABLE (sgc1.col_list) myc

CauseHere are the steps to reproduce the issue:

1. Create a table under "TEST0" schema:

$ sqlplus test0/test0

DROP TABLE TEST0 PURGE;

CREATE TABLE TEST0

(

COL1 NUMBER NOT NULL,

COL2 NUMBER NOT NULL,

COL3 NUMBER NOT NULL

)

TABLESPACE USERS;

ALTER TABLE TEST0 ADD (PRIMARY KEY (COL2, COL1, COL3) USING INDEX TABLESPACE USERS);

CREATE INDEX TEST0INDEX ON TEST0 (COL3, COL1, COL2) TABLESPACE USERS;

select object_name, object_type from user_objects;

OBJECT_NAME OBJECT_TYPE

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

TEST0 TABLE

SYS_C009845 INDEX <-- Index associated with PK constraint.

TEST0INDEX INDEX

2. Collect statistics for "TEST0" schema.

sqlplus system/{password}

exec dbms_stats.gather_schema_stats('TEST0',cascade => TRUE);

3. Execute the DataPump export an import process to reproduce the problem.

$ expdp system/oracle schemas=test0 dumpfile=test0.dmp

... (completed successfully without warnings)

$ impdp system/oracle remap_schema=test0:test1 dumpfile=test0.dmp

.

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

ORA-39083: Object type INDEX_STATISTICS failed to create with error:

ORA-01403: no data found

Failing sql is:

DECLARE IND_NAME VARCHAR2(60); IND_OWNER VARCHAR2(60); BEGIN DELETE FROM "SYS"."IMPDP_STATS";

SELECT index_name, index_owner INTO IND_NAME, IND_OWNER

FROM (SELECT UNIQUE sgc1.index_name, sgc1.index_owner,

COUNT(*) mycount

FROM sys.ku$_find_sgc_view sgc1,

TABLE (sgc1.col_list) myc

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

4.) Open a sqlplus session to check objects created for the import process under "TEST1" schema.

$ sqlplus test1/{password}

col object_name for a30

SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS;

OBJECT_NAME OBJECT_TYPE

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

TEST0 TABLE

TEST0INDEX INDEX

One index is missing, for this reason the impdp utility fails importing the statistics associated to that particular missing index. In this case, the problem is generated because expdp utility puts the CREATE INDEX statements in wrong order into the dumpfile. This causes impdp utility to create a primary key constraint using a wrong index (TEST0INDEX).

This behavior is reported in Bug 6156708.

Reference: Bug 6156708 expdp puts the index creation statements into dump in wrong order

Solution1. Use conventional export / import or

2. Do a datapump import excluding indexes, then import indexes.

(Doing that we guarantee that the index associated to the primary key constraints will be created first).

$ impdp system/oracle remap_schema=test0:test1 exclude=index dumpfile=test0.dmp

$ impdp system/oracle remap_schema=test0:test1 include=index dumpfile=test0.dmp

Output of the import process (include=index)

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

The following query can be executed to check the objects created after the import process is completed.

$ sqlplus test1/{password}

col object_name for a30

SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS;

OBJECT_NAME OBJECT_TYPE

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

TEST0 TABLE

SYS_C009868 INDEX

TEST0INDEX INDEX

ReferencesBUG:6156708- EXPDP PUTS THE INDEX CREATION STATEMENTS INTO DUMP IN WRONG ORDER

##########################################################################

童鞋们，看看吧，还说什么呢！

于是，再次做了一个草率的决定---要求客户打这个补丁---p6156708_10204_Generic.zip

客户同意了我的决定，这个补丁打的比较顺利，过程如下：

##########################################################################

Patch Installation Steps:

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

1. Set your current directory to the directory where the patch is located.

cd <PATCH_TOP>/6156708

2. Apply the patch.

Use the following command to apply the patch to the ORACLE_HOME:

opatch apply

When OPatch starts, it will validate the patch and make sure there

are no conflicts with the software already installed in the ORACLE_HOME.

OPatch categorizes two types of conflicts:

(a) Conflicts with a patch already applied to the ORACLE_HOME

In this case, please stop the patch installation and contact

Oracle Support Services.

(b) Conflicts with subset patch already applied to the ORACLE_HOME

In this case, please continue the install, as the new patch

contains all the fixes from the existing patch in the ORACLE_HOME.

The subset patch will automatically be rolled back prior to the

installation of the new patch.

Patch Post Install Instructions:

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

After the patch has been applied please reload the package into

the database. To do this connect as SYS and execute the following;

sqlplus "/ as sysdba"

SQL> @?/rdbms/admin/dbmsmeta.sql

SQL> @?/rdbms/admin/dbmsmeti.sql

SQL> @?/rdbms/admin/dbmsmetu.sql

SQL> @?/rdbms/admin/dbmsmetb.sql

SQL> @?/rdbms/admin/dbmsmetd.sql

SQL> @?/rdbms/admin/catmeta.sql

SQL> @?/rdbms/admin/prvtmeta.plb

SQL> @?/rdbms/admin/prvtmeti.plb

SQL> @?/rdbms/admin/prvtmetu.plb

SQL> @?/rdbms/admin/prvtmetb.plb

SQL> @?/rdbms/admin/prvtmetd.plb

SQL> @?/rdbms/admin/catmet2.sql

##########################################################################

面带悦色的要求客户再次导入试试。

当客户把导入日志发给我看的时候，我几乎疯了---错误依旧！

哎，换个思路，按照常规的方法来诊断解决吧。

首先查看数据库告警日志，有所发现：

Mon Mar 5 22:02:41 2012

GATHER_STATS_JOB encountered errors. Check the trace file.

Mon Mar 5 22:02:41 2012

Errors in file /u01/app/oracle/admin/ocrmpdb/bdump/ocrmpdb_j000_22888.trc:

ORA-20000: index "CRM20"."IDX_RATIONNO_DEALCONFIRM_TEMP" or partition of such index is in unusable state

并且这个错误前面也多次出现。

接着查看相关的跟踪文件：

*** 2012-03-05 22:02:41.676

GATHER_STATS_JOB: GATHER_TABLE_STATS('"CRM20"','"TINF_CONFIRM"','""', ...)

ORA-20000: index "CRM20"."IDX_RATIONNO_DEALCONFIRM_TEMP" or partition of such index is in unusable state

索引的状态为：

select status from dba_indexes where index_name='IDX_RATIONNO_DEALCONFIRM_TEMP';

STATUS

--------

UNUSABLE

于是尝试手动收集下该表的统计信息，报出下面的错误：

SQL> exec dbms_stats.gather_table_stats('CRM20','TINF_CONFIRM');

BEGIN dbms_stats.gather_table_stats('CRM20','TINF_CONFIRM'); END;

*

ERROR at line 1:

ORA-20000: index "CRM20"."IDX_RATIONNO_DEALCONFIRM_TEMP" or partition of such index isin unusable state

ORA-06512: at "SYS.DBMS_STATS", line 13437

ORA-06512: at "SYS.DBMS_STATS", line 13457

ORA-06512: at line 1

关键的，紧急的，救命的时刻，还得metalink出击---不过查询的结果有点小失望：

############################################################

CauseIndex is invalid.

Can check that running

select status from dba_objects where object_name = 'INDEX NAME';

SolutionRebuild the index and gather new stats on the related table

Example:

alter index <INDEX NAME> rebuild;

##############################################################

经过客户的同意，对IDX_RATIONNO_DEALCONFIRM_TEMP索引做了一个rebuild操作：

SQL> alter index "CRM20"."IDX_RATIONNO_DEALCONFIRM_TEMP" rebuild online;

Index altered.

再次查看索引的状态，变成了：

SQL> select status from dba_indexes where index_name='IDX_RATIONNO_DEALCONFIRM_TEMP';

STATUS

--------

VALID

手动收集该表的统计信息也成功了：

SQL> exec dbms_stats.gather_table_stats('CRM20','TINF_CONFIRM');

PL/SQL procedure successfully completed.

再次导出导入，正常，万岁！

低调吧，别再轻易地给出草率的结论了！

《道德经》里老子说的好：

重为轻根，静为躁君；轻则失本，躁则失君！

ITPub博客

数据迁移出现ORA-39083， ORA-01403，ORA-01403

最新文章