ORA-39117 Incorrectly Reported At Impdp Level
Last updated on MARCH 11, 2009
Applies to:Oracle Server Enterprise Edition - Version: 10.2.0.1 to 126.96.36.199
This problem can occur on any platform.
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 188.8.131.52
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
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.
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.
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