Friday, October 20, 2023

ORA-02304: invalid object identifier literal and ORA-39083 errors during impdp

Users might receive below error during the import.

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

Failing sql is:
className1 VARCHAR2(200),

Run below query in the database and check for TPYE_ODI exists or not
SQL> select OWNER, TYPE_NAME from DBA_TYPES where TYPE_OID='FB3CF41KK327B011D053F114ABBC878C ';

------------------------------------------ --------------------------------
ORCL      FB3CF41KK327B011D053F114ABBC878C

In most cases you will see the ODI is already exists in the database. The ODI already exists, and you cannot create ODI with same name that’s why it failed. You need to follow one of the options.

1. Create the object with new ODI – You can remove the ODI clause from the statement and run again. The database will generate new ODI for the object.

2. Drop the existing TYPE_ODI and reimport - DROP the object and reimport so that object will create with same ODI

3. In “impdp” use the parameter transform=oid:n - Using the parameter during the impdp will import TYPES with new ODI.

I have used all 3 methods based upon different scenarios.

Thanks & Regards,

1 comment:

  1. Dealing with those pesky "invalid object identifier literal and ORA-39083" errors during impdp can be a bit of a head-scratcher, but fear not! 🤔🛠️ These challenges are like little puzzles for our tech-savvy minds. 💻💡 Double-check your object identifiers, ensure they're spot on, and maybe sprinkle a bit of troubleshooting magic. 🧙‍♂️ If the hiccup persists, take a deep breath

    Pay Someone To Take My Class For Me