ORA-02304: invalid object identifier literal while executing IMPDP

While cloning a schema within the same database, I encountered the following exception:

ORA-39083: Object type TYPE:"NEW_DUMMY_USER"."TYPE1" failed to create with error:
ORA-02304: invalid object identifier literal

While I was interested in importing only table data, I have created the following PARFILE:

DUMPFILE=export_file.dmp
DIRECTORY=EXP_DIR
REMAP_SCHEMA=DUMMY_USER:NEW_DUMMY_USER
LOGFILE=DUMMY_USER_new.log
EXCLUDE=CONSTRAINT
EXCLUDE=REF_CONSTRAINT
EXCLUDE=GRANT
EXCLUDE=FUNCTION
EXCLUDE=PROCEDURE
EXCLUDE=PACKAGE
EXCLUDE=TABLE_STATISTICS
EXCLUDE=INDEX_STATISTICS

And the execution was looking like this:

oracle@server1:db1:[~/marius]$ impdp  \'/ as sysdba\' PARFILE=parfile.par

Import: Release 18.0.0.0.0 - Production on Mon Feb 24 15:11:11 2020
Version 18.7.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  "/******** AS SYSDBA" PARFILE=parfile.par
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
ORA-39083: Object type TYPE:"NEW_DUMMY_USER"."TABLE1" failed to create with error:
ORA-02304: invalid object identifier literal

Failing sql is:
CREATE EDITIONABLE TYPE "NEW_DUMMY_USER"."TABLE1"   OID '57A49595BB0B3B63E0401AACC29C0BCD' AS object (col1 VARCHAR2(64), col2 VARCHAR2(4000))

ORA-39083: Object type TYPE:"NEW_DUMMY_USER"."TABLE2" failed to create with error:
ORA-02304: invalid object identifier literal

Failing sql is:
CREATE EDITIONABLE TYPE "NEW_DUMMY_USER"."TABLE2"   OID '57A49595BB1C3B63E0401AACC29C0BCD' AS object (col1 VARCHAR2(45))

ORA-39083: Object type TYPE:"NEW_DUMMY_USER"."table2" failed to create with error:
ORA-02304: invalid object identifier literal
........................................

This is happening due to the fact that when executing the import, the impdp is trying to create those objects with the same OID (as I said, I’m cloning a schema in the same database). After doing some research, I found out this parameter TRANSFORM will take care of this. It has to options Y and N. If we choose Y, the objects will be created with the same OID, if we choose N, a new OID will be generated. The second option is useful in my case (when one is clone a schema within the same database). And now the PARFILE is looking like this:

DUMPFILE=export_file.dmp
DIRECTORY=EXP_DIR
REMAP_SCHEMA=DUMMY_USER:NEW_DUMMY_USER
LOGFILE=DUMMY_USER_new.log
EXCLUDE=CONSTRAINT
EXCLUDE=REF_CONSTRAINT
EXCLUDE=GRANT
EXCLUDE=FUNCTION
EXCLUDE=PROCEDURE
EXCLUDE=PACKAGE
EXCLUDE=TABLE_STATISTICS
EXCLUDE=INDEX_STATISTICS
TRANSFORM=OID:N

The import had no issues.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.