Wednesday, February 1, 2017

Getting error while doing impdp ORA-39083: Object type TYPE failed to create with error

I was getting the error while perfomring improt

ORA-39083: Object type TYPE:"HARVEY"."HARTYP_COLUMN" failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE EDITIONABLE TYPE "HARVEY"."HARTYP_COLUMN"   OID '3EB78B505CC15B2EE0531560060A482A' AS OBJECT (
  col  NUMBER (19, 0)
);
ORA-39083: Object type TYPE:"HARVEY"."HARTYP_RECORD" failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE EDITIONABLE TYPE "HARVEY"."HARTYP_RECORD"   OID '3EB7A19BA0923593E0531560060AABAA' AS OBJECT (
  component  VARCHAR2 (20)
 ,from_id    NUMBER (19, 0)
 ,new_id     NUMBER (19, 0)
);
ORA-39083: Object type TYPE:"HARVEY"."RMTYP_STRING" failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE EDITIONABLE TYPE "HARVEY"."RMTYP_STRING"   OID '3EC89CAF1D642B69E0531760060AD984' AS VARRAY(200) OF NVARCHAR2(2000);
ORA-39083: Object type TYPE:"HARVEY"."RMTYP_TABLE" failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE EDITIONABLE TYPE "HARVEY"."RMTYP_TABLE"   OID '3EB78B505CC55B2EE0531560060A482A' AS TABLE OF HARTYP_COLUMN;
ORA-39083: Object type TYPE:"HARVEY"."RMTYP_MAPTABLE" failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE EDITIONABLE TYPE "HARVEY"."RMTYP_MAPTABLE"   OID '3EB7A19BA0983593E0531560060AABAA' AS TABLE OF HARTYP_RECORD


Reason:

This error occurs was occuring because I was doing the remap_schema and remap_tablespace .

In order of fix this what I have done was introduced a parameter in impdp which is transform=OID:n the default value is OID:y which means copy the original tablespace location.

Stating transform=OID:n means do not copy the original tablespace location because we are moving to different tablespace.

and the import was all good.

No comments: