IMP same library Type object import error ORA-02304

Type is a database object structure that we often use. In practice, we can define the type separately and then use it in PLSQL code or data tables. On an accidental Machine

Type is a database object structure that we often use. In practice, we can define the type separately and then use it in PL/SQL code or data tables. On an accidental Machine

Type is a database object structure that we often use. In practice, we can define the type separately and then use it in PL/SQL code or data tables.

In an accidental opportunity, I found that the use of the Type is troublesome in data exp/imp. When we use the exp/imp tool to copy data between different schemas of the same database Instance, if the Schema has a type, the error will occur.

We have proved it through a series of experiments.

1. Prepare the experiment environment

We use 10gR2 as the lab database.


SQL> conn scott/tiger @ ots;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as scott

SQL> select * from v $ version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod
PL/SQL Release 10.2.0.1.0-Production
CORE 10.2.0.1.0 Production


First, we export all objects in the scott user schema. Note: currently, scott has some data type objects.


SQL> select type_name, type_oid, typecode from user_types;

TYPE_NAME TYPE_OID TYPECODE
--------------------------------------------------------------------------------------------
CUST_ADDRESS_TYPE_NEW 0239FC5ABD78464D8D6C4D7085E2F549 OBJECT
T_REC_TEST 428a1b3c7e1e4a3cb2063b93624253ea OBJECT
T_REC_TABLE D9AFD3FAE0A54964B1684CA28C69CEED COLLECTION
T_TYP 8E294AB7CC28493A94FF82791A376379 OBJECT
N_TYP 338172b842554bab8c26d4c27b5908f1 OBJECT


In Oracle, each type is assigned a unique oid number as an internal identifier. Next, we use the exp tool to export scott user objects.


D: \> exp scott/tiger @ ots file = scott_20120606.dmp indexes = y rows = y compress = y cons
Traints = y wner = scott

Export: Release 10.2.0.1.0-Production on Wednesday June 6 17:22:16 2012
Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options
The ZHS16GBK and AL16UTF16 NCHAR character sets have been exported.

Will export the specified user...
. Exporting objects and operations in the pre-schema Process
. Exporting User SCOTT's external function library name
. Export PUBLIC type Synonyms
. Exporting the special type Synonym
. Exporting User SCOTT's object type definition
(The reason for the length is omitted ......)
The export is successfully terminated without a warning.

D: \>


Then, we create the same database user scottback.


SQL> create user scottback identified by scottback;
User created

SQL> grant resource to scottback;
Grant succeeded

SQL> grant connect to scottback;
Grant succeeded

SQL> grant exp_full_database to scottback;
Grant succeeded

SQL> grant imp_full_database to scottback;
Grant succeeded


2. Data Import

An error occurs when we try to import data to the same database.


D: \> imp scottback/scottback @ ots file = scott_20120606.dmp indexes = y rows = y constra
Ints = y ignore = y fromuser = scott touser = scottback

Import: Release 10.2.0.1.0-Production on Wednesday June 6 17:34:21 2012

Copyright (c) 1982,200 5, Oracle. All rights reserved.
Connect to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the Partitioning, OLAP and Data Mining options

EXPORT File Created by EXPORT: V10.02.01 in the normal path

Warning these objects are exported by SCOTT instead of the current user.

The ZHS16GBK Character Set and AL16UTF16 NCHAR character set have been imported.
IMP-00017: The following statement failed due to ORACLE error 2304:
"Create type" T_REC_TEST "TIMESTAMP '2017-12-21: 18: 17: 30 'OID '428a1b3c7e1e4"
"A3cb2063b93624253ea 'as object ("
"Id number );"
""
""
IMP-00003: encountered ORACLE error 2304
ORA-02304: Invalid object identifier text
IMP-00017: The following statement failed due to ORACLE error 2304:
"Create type" T_REC_TABLE "TIMESTAMP '2017-12-21: 18: 17: 33 'OID 'd9afd3fae0a5"
"4964B1684CA28C69CEED 'as table of t_rec_test ;"
""
""
IMP-00003: encountered ORACLE error 2304
ORA-02304: Invalid object identifier text
IMP-00017: The following statement failed due to ORACLE error 2304:
"Create type" T_TYP "TIMESTAMP '2017-03-07: 10: 47: 03 'id' 8E294AB7CC28493A94"
"FF82791A376379 'as object (id number );"
""
""
IMP-00003: encountered ORACLE error 2304
ORA-02304: Invalid object identifier text
IMP-00017: The following statement failed due to ORACLE error 2304:
"Create type" N_TYP "TIMESTAMP '2017-03-07: 11: 03: 01 'id' 338172b810754bab8c"
"26D4C27B5908F1 'as object (t_id number, t_name varchar2 (10), t_addr varchar"
"2 (20 ));"
""
""
IMP-00003: encountered ORACLE error 2304
ORA-02304: Invalid object identifier text
IMP-00017: The following statement failed due to ORACLE error 2304:
"Create type" CUST_ADDRESS_TYPE_NEW "TIMESTAMP '2017-05-23: 16: 15: 03 'id' 02"
"39FC5ABD78464D8D6C4D7085E2F549 'as object"
"(Street_address varchar2"
"(40 ),"
"Postal_code varchar2 (10 )"
", City varchar2 (30 )"
", State_province varchar2 (10 )"
", Country_id char (2 )"
");"
""
""
IMP-00003: encountered ORACLE error 2304
ORA-02304: Invalid object identifier text
(Part of the reason for length is omitted .....)

ORA-02270: the unique or primary key for this column list does not match
Constraints to be enabled...
Import is terminated successfully, but a warning is displayed.


From the log information, we can see that when creating a type variable, Oracle Reports an error 2304. With the oerr tool, we can check the error information.


[Oracle @ bspdev ~] $ Oerr ora 1, 2304
02304,000 00, "invalid object identifier literal"
// * Cause: An attempt was made to enter an object identifier literal
// Create type that is either:
//-Not a string of 32 hexadecimal characters
//-An object identifier that already identifies an existing
// Object
//-An object identifier different from the original object
// Identifier already assigned to the type
// * Action: Do not specify the object identifier clause or specify a 32
// Hexadecimal-character object identifier literal that is unique
// Or identical to the originally assigned object identifier. Then
// Retry the operation.



Literally, an error occurs in the command statement that creates the type. From the script information, it is very special to create a type statement in the displayed script, with timestamp and oid information in the middle. And corresponds to the original schema.