expdp/impdp ORA-39083 ORA-1403 问题处理

APPLIES TO:

Oracle Database - 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.
***Checked for relevance on 28-MAR-2013***

SYMPTOMS

A 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

CAUSE

Here are the steps to reproduce the issue:

1. Create a table under "TEST0" schema:

#> sqlplus test0/password

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/password schemas=test0 dumpfile=test0.dmp 
...

(completed successfully without warnings) 

#> impdp system/password 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 .

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

SOLUTION

1. Use conventional export/import (exp/imp)

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/password remap_schema=test0:test1 exclude=index dumpfile=test0.dmp 
#> impdp system/password 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


另外一种情况，如果表中包含Default value字段，有可能触发Bug 8543770

Bug 8543770  ORA-39083 / ORA-1403 from IMPDP on INDEX_STATISTICS

 This note gives a brief overview of bug 8543770. 
 The content was last updated on: 28-JUN-2013
 Click here for details of each of the sections below.

Affects:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions BELOW 12.1
Versions confirmed as being affected	11.2.0.1 11.1.0.7
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in

12.1.0.1 (Base Release) 11.2.0.2 (Server Patch Set) 11.2.0.1 Bundle Patch 4 for Exadata Database 11.2.0.1 Patch 1 on Windows Platforms

Symptoms:	Related To:
Corruption (Dictionary) Error May Occur Performance Of Query/ies Affected Unstable Execution Plan ORA-39083	Datapump Export/Import

Description

When importing index statistics with IMPDP and ORA-39083 may be reported 
if the table has DEFAULT column values.
eg: 
 IMPDP may fail with:
  ORA-39083: Object type INDEX_STATISTICS failed to create with error:
  ORA-01403: no data found
 
Another symptom is corrupted statistic values silently inserted to
dba_tab_col_statistics after datapump import. This may cause bad 
execution plan to be generated, or report ORA-600[19004] if the 
histogram values are corrupted in a certain manner and fix to bug:10627631 is not applied.