impdp ORA-39082 ORA-2304

1. 创建用户

CONNECT system/password
CREATE USER u1 IDENTIFIED BY u1 DEFAULT TABLESPACE users;
CREATE USER u2 IDENTIFIED BY u2 DEFAULT TABLESPACE users;
ALTER USER u1 QUOTA UNLIMITED ON users;
ALTER USER u2 QUOTA UNLIMITED ON users;
GRANT create session, create table, create type TO u1;
GRANT create session, create table, create type TO u2;

2. u1用户下创建type

CONNECT u1/u1
CREATE TYPE my_coltype AS OBJECT (nr NUMBER, txt VARCHAR2(10))
/
CREATE TYPE my_tabtype AS OBJECT (nr NUMBER, txt VARCHAR2(10))
/
CREATE TABLE my_reltab (nr NUMBER, col1 my_coltype);
CREATE TABLE my_objtab OF my_tabtype;
INSERT INTO my_reltab VALUES (1, my_coltype(1, 'Line 1'));
INSERT INTO my_objtab VALUES (1, 'Line 1');
COMMIT;

3. 导出u1用户

expdp system/password DIRECTORY=my_dir DUMPFILE=expdp_u1.dmp LOGFILE=expdp_u1.log SCHEMAS=u1

4. 导入到u2，报错

impdp system/password DIRECTORY=my_dir DUMPFILE=expdp_u1.dmp LOGFILE=impdp_u1.log 
REMAP_SCHEMA=u1:u2
...
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "U2"."MY_COLTYPE"   OID 'DDB334945FA24A41AC0099E457715B62' as ...
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "U2"."MY_TABTYPE"   OID 'EEC16EAE6DF34B4FA755DBB448EC4F78' as ...
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 "U2"."MY_RELTAB" ...
ORA-39117: Type needed to create table is not included in this operation. Failing sql is:
CREATE TABLE "U2"."MY_OBJTAB" OF "U2"."MY_TABTYPE"  ...

5. TRANSFORM参数

该参数是从10.2开始才有的 TRANSFORM参数设置成OID=N,表示在imp的时候，新创建的表或这个类型会赋予新的OID，而不是dmp文件中包含的OID的值。但是这个参数的默认值是OID=Y，因此在进行Imp的时候，新创建的表或者type会赋予同样的OID，如果是位于同一个数据库上的不同schema，那就会造成OID冲突的问题，因此解决这个问题也很简单，只需要在impdp的时候，显示设置transform 参数为OID=N既可以了

6. 解决

impdp system/password DIRECTORY=my_dir DUMPFILE=expdp_u1.dmp LOGFILE=impdp_u1.log
REMAP_SCHEMA=u1:u2 TRANSFORM=oid:n
...
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "U2"."MY_OBJTAB"                         6.125 KB       1 rows
. . imported "U2"."MY_RELTAB"                         6.218 KB       1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 12:38:56

参考：DataPump Import Of Object Types Fails With Errors ORA-39083/ORA-39082 ORA-2304 Or ORA-39117 ORA-39779 (Doc ID 351519.1)