ORA-39083: Object type TYPE failed to create with error:ORA-02304: invalid object identifier literal
Failing sql is:CREATE TYPE "ACCTEST"."SPLIT_TBL1" OID 'DD944536D2EB453B8A965D8502B03C14' AS TABLE OF CLOB
Cause : Oracle was trying to import an object with OID which already existed in database. OID should be unique in a database.
Solution : I added the clause transform=OID:n in my impdp command.
Solution : I added the clause transform=OID:n in my impdp command.
========================================================================
SQL>
SQL> host impdp ACCTEST/log directory=imports dumpfile=2029012015-CORPORATE.DMP
remap_schema=CORPORATE:ACCTEST logfile=ACCTEST.txt full=y;
Import: Release 11.2.0.3.0 - Production on Thu Mar 5 15:46:13 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 "ACCTEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ACCTEST"."SYS_IMPORT_FULL_01": ACCTEST/******** directory=imports dum
pfile=2029012015-CORPORATE.DMP remap_schema=CORPORATE:ACCTEST logfile=ACCTEST.tx
t full=y
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ACCTEST" already exists
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "ACCTEST"."SPLIT_TBL" OID 'ECD98FAC59A9469F8E458BDC2567F955' AS TA
BLE OF VARCHAR2 (32767)
Solution:
host impdp ACCT/log directory=imports dumpfile=2029012015-CORPORATE.DMP remap_schema=CORPORATE:ACCT logfile=ACCTEST.txt transform=OID:n full=y;
SQL> host impdp ACCT/log directory=imports dumpfile=2029012015-CORPORATE.DMP rem
ap_schema=CORPORATE:ACCT logfile=ACCTEST.txt transform=OID:n full=y;
Import: Release 11.2.0.3.0 - Production on Thu Mar 5 15:55:18 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 "ACCT"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ACCT"."SYS_IMPORT_FULL_01": ACCT/******** directory=imports dumpfile=
2029012015-CORPORATE.DMP remap_schema=CORPORATE:ACCT logfile=ACCTEST.txt transfo
rm=OID:n full=y
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ACCT" already exists
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/DB_LINK
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ACCT"."MVW_COSTOFGOODS_V2" 7.541 GB 51376158 rows
. . imported "ACCT"."STOCKVALUE" 5.582 GB 35892288 rows
. . imported "ACCT"."MVW_STOCKVALUESUM" 5.081 GB 31559509 rows
. . imported "ACCT"."MVW_COSTOFGOODS_V2_130614" 4.568 GB 31653770 rows
. . imported "ACCT"."SALESINVOICEDTL" 4.388 GB 34245812 rows
. . imported "ACCT"."SALESINVOICEHDR" 4.020 GB 14193074 rows
. . imported "ACCT"."TB_COSTOFGOODS2_2014" 2.337 GB 15613672 rows
. . imported "ACCT"."SALESINVOICEDTL0404" 2.163 GB 17440925 rows
. . imported "ACCT"."MVW_STOCKBATCHVALUE" 1.964 GB 20554589 rows
