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

来源：这里教程网时间：2026-03-03 12:10:39 作者：

今天安装windows系统下的oracle数据库，版本是11.2.0.1.0在，从linux系统的11.2.0.4.0版本的数据库导出，导入至windows系统下的oracle数据库下的出现报错： ——————————————————————————————————————————————————— ORA-39083: 对象类型 INDEX_STATISTICS 创建失败, 出现错误: ORA-01403: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到任何数据 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: 未找到数据 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 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 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. 再次导出导入，正常，万岁！低调吧，别再轻易地给出草率的结论了！《道德经》里老子说的好：重为轻根，静为躁君；轻则失本，躁则失君！

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推