impdp报错ORA-39083 ORA-02304 Object type TYPE failed to create

郑全

                        

环境
Red Hat Enterprise Linux Server release 7.4
ORACLE Release 11.2.0.3.0 Production

我用expdp,impdp复制一个shema，在impdp导入的时候报错

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

ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "xxx"."TY_STR_SPLIT"   OID 'E2E3F67932DD46068284F7381C69E82B' IS TABLE OF VARCHAR2 (4000)

查看官方文档的impdp章节，搜索OID,发现了如下有用的内容
查看impdp help=y 可以使用一个参数：transform

TRANSFORM
Enables you to alter object creation DDL for objects being imported.

OID - If the value is specified as n, the assignment of the exported OID during the
creation of object tables and types is inhibited. Instead, a new OID is assigned.
This can be useful for cloning schemas, but does not affect referenced objects. The
default value is y.

这个参数默认是Y，如果改成N，那么导入的时候会把各个对象重新赋予新的OID。
transform=oid:n
这样，再用impdp导入的时候就不会出现
ORA-39083，ORA-02304
注意：exp/imp不支持oid转换，又是一个使用数据泵的优势。

因此我想到了两种方法可以解决问题
1.用下面impdp语句重新导入
impdp system/'*****' directory=dir_lisx REMAP_SCHEMA=xxx:yyy dumpfile=wmsuat20150304.dump include=TYPE transform=oid:n logfile=impdp_wmsuat20150304_2.log

Import: Release 11.2.0.3.0 - Production on Wed Mar 4 14:55:40 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dir_lisx REMAP_SCHEMA=WMSUAT:GENIDCDEV2 dumpfile=wmsuat20150304.dump include=TYPE transform=oid:n logfile=impdp_wmsuat20150304_2.log
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 14:55:45

2.手工重建刚才导入失败的type
核查type的ddl语句
SELECT dbms_metadata.get_ddl('TYPE','ARRAY_TYPE2','WMSUAT') from dual ;

CREATE OR REPLACE TYPE "WMSUAT"."ARRAY_TYPE2" is array(48) of varchar2(255) ;

记得重新编译一下新clone的schema
EXEC DBMS_UTILITY.compile_schema(schema => 'GENIDCDEV2');