APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.3 to 11.2.0.4 [Release 10.2 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
NOTE: In the images and/or the document content below, the user information and data used represents fictitious data. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.
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
CHANGES
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
5. The index with the system generated name is missing because the primary key constraint was created in the source database without providing a name and before the named index was created, so an index with a system generated name was created to facilitate this primary key constraint. Upon import, Data Pump first imports the indexes (i.e. only the named index TEST0INDEX), and then imports the constraints. When creating the primary key constraint, we can use the existing named index, so there is no need for the system generated index anymore, and hence it won't be created. When import tries to import the statistics for the system generated index later on, it fails because the system generated index is missing.
This behavior was reported in:
Bug 8615836 - ORA-1403 OCCURS DURING IMPORTING TABLE_STATISTICS IN IMPDP, closed as not feasible to fix.
SOLUTION
1. Ignore the ORA-39083 (Object type INDEX_STATISTICS failed to create with error) and ORA-01403 (no data found) on this system generated index.
-- or --
2. Ensure you create named indexes for PK constraints in the source database. E.g.:
-- instead of:
ALTER TABLE TEST0 ADD (PRIMARY KEY (COL2, COL1, COL3) USING INDEX TABLESPACE USERS);
-- do:
ALTER TABLE TEST0 ADD CONSTRAINT MY_PK PRIMARY KEY (COL2, COL1, COL3) USING INDEX TABLESPACE USERS);
-- or --
3. 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
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
-- or --
4. Use conventional export/import (exp/imp) (not recommended).
REFERENCES
BUG:8615836 - ORA-1403 OCCURS DURING IMPORTING TABLE_STATISTICS IN IMPDP
NOTE:1326355.1 - Bug 6156708 Not Fixed In 11.0.1.7 - Impdp fails when importing stats : ORA-39083, ORA-01403
NOTE:1455492.1 - Why Does The Primary Key Constraint Point To A Different Index After Import?
