Home > TroubleShooting > ORA-39083: Object type TABLE failed to create with error:

ORA-39083: Object type TABLE failed to create with error:

Symptoms

Today,  I transfer a schema from 10.2.0.3 database to 11.1.0.7 database. All tables can be transferred by expdp/impdp except of one with following when it is imported by imdp

With the Real Application Clusters option
Master table “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_TABLE_01″:  system/******** parfile=imp_sgcmsqa_tab.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE failed to create with error:
ORA-00922: missing or invalid option
Failing sql is:
CREATE TABLE “CMSADM”.”VOD_METADATA_UPLOAD” (“ID” NUMBER(19,0) NOT NULL ENABLE, “UPLOAD_DATA” BLOB, “FILE_NAME” VARCHAR2(100 BYTE), “IS_LOAD” NUMBER(1,0), “IS_VALIDATE” NUMBER(1,0), “LAST_LOAD_DATE” TIMESTAMP (6), “LIBRARY_ID” VARCHAR2(30 BYTE) NOT NULL ENABLE, “IS_DELETE” NUMBER(1,0), “VERSION” NUMBER(10,0) NOT NULL ENABLE, “UPDATER_ID” NUMBER(19,0), “AUDIT_ID” NUMBER(19,0), “CREATOR_ID” NUMBER(
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:”CMSADM”.”VOD_METADATA_UPLOAD_PK” skipped, base object type TABLE:”CMSADM”.”VOD_METADATA_UPLOAD” creation failed
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:”CMSADM”.”VOD_METADATA_UPLOAD_PK” skipped, base object type TABLE:”CMSADM”.”VOD_METADATA_UPLOAD” creation failed
Job “SYSTEM”.”SYS_IMPORT_TABLE_01″ completed with 3 error(s) at 16:27:36

Fixed

Add version option as the following impdp parfile, it is fixed.

dumpfile=EXPDMP:sgcmsqa.dmp
LOGFILE=expdmp:imp_sgcmsqa_tab.log
TABLE_EXISTS_ACTION=REPLACE
version=10.2.0.3 <– add this option
REMAP_SCHEMA=(CMSADM:CMSADM)
tables=VOD_METADATA_UPLOAD

$ impdp system/oracle parfile=imp_sgcmsqa_tab.par

Import: Release 11.1.0.7.0 – 64bit Production on Wednesday, 13 January, 2010 16:40:19

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Release 11.1.0.7.0 – 64bit Production
With the Real Application Clusters option
Master table “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_IMPORT_TABLE_01″:  system/******** parfile=imp_sgcmsqa_tab.par
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported “CMSADM”.”VOD_METADATA_UPLOAD”              6.111 MB      47 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYSTEM”.”SYS_IMPORT_TABLE_01″ successfully completed at 16:40:37

Advertisements
Categories: TroubleShooting
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: