ORA-39117 Incorrectly Reported At Impdp Level (Doc ID 783358.1)

Last updated on MARCH 11, 2009

Applies to:

Oracle Server Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7
This problem can occur on any platform.
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7

Symptoms

 A schema level export is taken with DataPump. This process completes without errors. 
The import of a specific schema produces the following error:  

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded  
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** dumpfile=test.dmp  
directory=feldir remap_schema=test:test1 transform=oid:n  
logfile=test_impdp.log  
Processing object type SCHEMA_EXPORT/USER  
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT  
Processing object type SCHEMA_EXPORT/ROLE_GRANT  
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE  
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA  
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC  
Processing object type SCHEMA_EXPORT/TABLE/TABLE  
ORA-39117: Type needed to create table is not included in this operation.  
Failing sql is:  
CREATE TABLE "TEST1"."TAB_BASECONCEPTS"("ID" NUMBER, "ENTITY"  
"TEST1"."T_ENTITY" ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS
LOGGING STORAGE NITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE  
"USERS" NESTED TABLE TREAT("ENTITY" AS "T_BASECONCEPT")."PROPERTIES" STORE  
AS "NESTEDTABLE1"(PCTFREE 10 PCTUSED 40 INITRANS  
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA  

 The types involved in the problematic create table statement are created successfully during impdp execution.
So, the error ORA-39117 should not be reported.  

Changes

  The issue is caused by TREAT AS option. Some 'treat as' types don't have the schema of the type in the src$ code. When a create table is being performed from a different schema, the parsing code assumes the type is in the schema performing the import instead of the owner of the table.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms