IMP-00003/ORA-902 when importing table with types (Doc ID 272771.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.1.0.2
This problem can occur on any platform.

Symptoms

Importing tables that has type definitions defined fails with IMP-00003: ORACLE error 902 encountered

Export with table mode is being used to export tables

This table has type / sub type definitions.

For example, type2 is a subtype of type1 and the table tab1 has a column
of type2

While importing this table you get error:
IMP-00003: ORACLE error 902 encountered
ORA-00902: invalid datatype

Here is the testcase:
==============
SQL> create or replace type type1 as object (c1 number) not final;
2 /

Type created.

SQL> create or replace type type2 under type1 (c2 number);
2 /

Type created.

SQL> -- create a table with a subtype as the attribute.
SQL> create table tab1 (dummy1 number, object_column1 type2);

Table created.

SQL> insert into tab1 values (1, type2(2,3));

1 row created.

SQL> host exp arraman/arraman file=expfile.dmp tables=tab1

Export: Release 9.2.0.1.0 - Production on Sat May 15 14:20:14 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table TAB1 0 rows exported
Export terminated successfully without warnings.

SQL> drop table tab1;

Table dropped.


SQL> drop type type2;

Type dropped.

SQL> drop type type1;

Type dropped.

SQL> host imp arraman/arraman file=expfile.dmp full=y

Import: Release 9.2.0.1.0 - Production on Sat May 15 14:21:38 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing ARRAMAN's objects into ARRAMAN
IMP-00017: following statement failed with ORACLE error 902:
"CREATE TABLE "TAB1" ("DUMMY1" NUMBER, "OBJECT_COLUMN1" "TYPE2") PCTFREE 10"
" PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREEL"
"IST GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 902 encountered
ORA-00902: invalid datatype
Import terminated successfully with warnings.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms