ORA-02304
When we copy all data from one schema to another through a loopback database link, we saw ORA-02304.
[oracle@test ~]$ impdp system@orclpdb schemas=ERP remap_schema=ERP:ERP2 network_link=ORCLPDB
...
ORA-39083: Object type TYPE:"ERP2"."TP_NEW_CACL" failed to create with error:
ORA-02304: invalid object identifier literal
Failing sql is:
CREATE EDITIONABLE TYPE "ERP2"."TP_NEW_CACL" OID 'E5F591FA09560272E0530A153A0ED64E' AS OBJECT
(
record_add_id VARCHAR2(36),
bill_id VARCHAR2(36),
transact TIMESTAMP(6),
release NUMBER(5),
CONSTRUCTOR FUNCTION tp_new_cacl RETURN SELF AS RESULT
)
...
As you can see, the error message specifically complains the object identifier literal is invalid, we should focus on the problem and fix it.
ORA-02304 means that impdp tries to create TYPE for the target schema, but there already has the same OID (object identifier) for the object, which is not allowed in the database.
Solution
The unique OID can make TYPE to be shared across multiple databases. In this case, we don't need the uniqueness.
In order to import TYPE in the same database, we'd better to remove the optional OID clause from the statement, then run it again. The database system will generate a new OID for this object.
That is, we should run the script without OID 'object_identifier'.
CREATE EDITIONABLE TYPE "ERP2"."TP_NEW_CACL" AS OBJECT
(
record_add_id VARCHAR2(36),
bill_id VARCHAR2(36),
transact TIMESTAMP(6),
release NUMBER(5),
CONSTRUCTOR FUNCTION tp_new_cacl RETURN SELF AS RESULT
);
Problem solved.