Resolving ORA-39083,USER DEFINED TYPE FAILED WHILE IMPORT TO THE SAME DATABASE :

Hi,
We days back when I checked my log file after doing import,I have seen error
ORA-39083,after doing some research I resolved this error as given below,This error
can occur while using imp utility or impdp utility,I have mentioned the possible resolution for both the cases.

Issue:
User defined data type failed to create, when an import is performed to the same database (source and destination same)

Error:
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal

Cause:-
OID of the user defined type already exists.

Resolution:

1) With import utility:-
Use the parameter - “toid_novalidate”

2) With impdp Utility
Use the parameter - “TRANSFORM=oid:n”
Test case:-

1) Import utility:
---------------------

imp system fromuser=prod touser=dev log=test.log ignore=y file=exp.dmp toid_novalidate=dev.T_MERGE_KEY_COLUMN,dev.T_MERGE_CONFIGURATION,dev.T_MERGE_KEY_COLUMN_LIST,dev.T_MERGE_CONFIGURATION_LIST

Where the following are the tables, which contains user defined datatype.

dev.T_MERGE_KEY_COLUMN,
dev.T_MERGE_CONFIGURATION,
dev.T_MERGE_KEY_COLUMN_LIST,
dev.T_MERGE_CONFIGURATION_LIST

Import details shows as follows:-

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and UTF8 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
. importing PROD's objects into DEV
. . skipping TOID validation on type DEV.T_MERGE_KEY_COLUMN
. . skipping TOID validation on type DEV.T_MERGE_KEY_COLUMN_LIST
. . skipping TOID validation on type DEV.T_MERGE_CONFIGURATION
. . skipping TOID validation on type DEV.T_MERGE_CONFIGURATION_LIST
. . importing table "CFG_ETL_MERGE_PROCESS" 62 rows imported
. . importing table "MERGE_PROCESS_CONFIGURATION" 1116 rows imported
. . importing table "MERGE_PROCESS_KEY_COLUMN" 66 rows imported
About to enable constraints...
Import terminated successfully without warnings.


2)Impdp Utility:
-----------------

impdp emptest/pwd REMAP_SCHEMA=emptest:emphp TRANSFORM=oid:n DIRECTORY=IMP_DP_EMPTEST NETWORK_LINK=emptest LOGFILE=emptestdata5_29122010.log

Here we used network impdp, we can use this for Normal impdp as well.

Note: The parameter TRANSFORM=oid:n is useful only in Oracle 10g rel2.

Hope it helps.


Best regards,

Rafi.