Wednesday, December 11, 2013

datapump: ORA-02304: invalid object identifier literal

During datapump import, there were errors with object ID (OID). This probably coming from another user who created the datapump export with transform=OID:y. In order to get the datapump import going, simply insert the clause of transform=OID:n since the default is transform=OID:y

Failing sql is:
CREATE TYPE "TRD"."SETOF_XORP_LOCKED_RESOURCES_SP"   OID 'B0DDDACF8F8G1137E0400D0AC4181435' AUTHID DEFINER AS OBJECT(moref VARCHAR2(254),vc_id RAW(16),cpu_required NUMBER(10,0),mem_required NUMBER(10,0))
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "TRD"."SETOF_XKI_ID_MO_DEPLOY_COUNT"   OID 'B0DDDACF8F961137E9400D0AC4181435' AUTHID DEFINER AS OBJECT(vs_id RAW(16),root_dir_id NUMBER(10,0),ms_count NUMBER(10,0))
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "TRD"."SETOF_MO_ID"   OID 'B0DDDACF8FA81137E0400D0AC4181435' AUTHID DEFINER AS OBJECT(ms_id RAW(16))
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "TRD"."SETOFMSKOCKEDRXSOURCESSPSET"   OID 'B0DDDACF8F9C1137E0405D0AC4181435' AS TABLE OF setof_ms_locked_resources_sp
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE TYPE "TRD"."SETOFRPLTCKEDEESOURCESSPSET"   OID 'B0DDD6ACF8FA01137E000D0AC4181435' AS TABLE OF setof_rp_locked_resources_sp
ORA-39083: Object type TYPE failed to create with error:
ORA-02304: invalid object identifier literal


impdp TXD@txorcl DIRECTORY=tx_dump dumpfile=tx_export_6300414.dmp REMAP_SCHEMA=TXDU:TXD remap_tablespace=TXDU_DATA:TXD_DATA TRANSFORM=oid:n

3 comments:

  1. Thanks for all your help on this. This really saved my day.

    ReplyDelete
    Replies
    1. Glad that this blog gained you an extra day back :)

      Delete
  2. Thank you very much!

    ReplyDelete