PROBLEM:

While importing schema objects, got below errors.

ORA-39083: Object type TYPE:"PADADM10-."NAME_AND_COUNT_ARRAY" failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE EDITIONABLE TYPE -PADADM10-."NAME_AND_COUNT_ARRAY" OID -576CD3FFA14D4400E05400144FFFAA04- IS VARRAY(30) OF name_and_count_t;

ROOT CAUSE:

OID value of  TYPE should be unique in the database and So if try to import TYPE with SAME OID, it will fail with this error.

In the above error, the OID is OID -576CD3FFA14D4400E05400144FFFAA04-. So check whether that OID is already present or not.

SQL> select owner,type_oid from dba_types where TYPE_OID='576CD3FFA14D4400E05400144FFFAA04';

OWNER           TYPE_OID
--------------- --------------------------------
TASDB          576CD3FFA14D4400E05400144FFFAA04

As the OID has already existed in the database, so TYPE getting imported should be generated using NEW OID.

SOLUTION:

So the solution to avoid this type of error is to use TRANSFORM=oid:n parameter with impdp.

Impdp command will look as below

impdp dumpfile=TEST_%U.dmp logfile=TEST_imp.log directory=T remap_schema=TEST:TEST10 parallel=4 TRANSFORM=oid:n

This command will import the TYPES with NEW OIDs

impdpORA- TROUBLESHOOTING


Related Topics

ORA-32771: cannot add file to bigfile tablespace
ORA-01536: space quota exceeded for tablespace
ORA-30034: Undo tablespace cannot be specified as temporary tablespace
ORA-20101: TABLESPACE is not a ASSM (Automatic Segment Space Management) tablespace
ORA-16855: transport lag has exceeded specified threshold
ORA-32773: operation not supported for smallfile tablespace
ORA-32774: more than one file was specified for bigfile tablespace
TNS-12542: TNS:address already in use
ORA-02304: invalid object identifier literal while import with CREATE TYPE OID
TNS-01106: Listener using listener name has already been started

You May Also Like

How to change sysman password for oracle 12c cloud control
Purge AUD$ table using DBMS_AUDIT_MGMT
how to send mail using utl_mail in oracle 11g
Steps for upgrading oracle database to 19c using DBUA
SGA TARGET ADVISORY IN ORACLE
Generate custom report from OEM cloud control
How to move spfile from file system to ASM in RAC
How to change flash recovery area location
Query clause in oracle datapump expdp
Oswatcher tool for collecting server diagnostic information

From This Website

OGG-00665 OCI Error describe for query
Useful ADRCI commands in oracle
how to send mail using utl_mail in oracle 11g
How to drop a database in postgres
Find sessions consuming lot of CPU
CONTENT parameter in datapump
COLS & COLSEXCEPT FILTER in goldengate
How to move spfile from file system to ASM in RAC
COMPRESSION in datapump oracle
Clone a database using dbca command in oracle 19c -New feature