failure on import to new DDL for object table

From: Walt Borders <borderw_at_ironhilltech.com>
Date: 5 Dec 2001 05:14:01 -0800
Message-ID: <5bd8e607.0112050514.66eb39e5@posting.google.com>

I have two Oracle 8.1.7 data servers. One with a legacy table and the other server with a table with an additional nested table column. I have sucessfully exported the data off the legacy system. I would like to import the data into the re-defined table. Importing and creating the Old DDL presents no problems. Having a NULLs in the new column is acceptable.

How do I do this?

Legacy object table DDL

CREATE TYPE deposit_objtyp AS OBJECT(

id           NUMBER,
static_ntab     	static_ntabtyp

);
/

CREATE TABLE deposit_objtab OF deposit_objtyp( CONSTRAINT deposit_pk PRIMARY KEY(id)
)
NESTED TABLE opinion_ntab STORE AS opinion_store_ntab ;

New DDL, with a new nested table column, "motion_ntab":

CREATE TYPE deposit_objtyp AS OBJECT(

id           NUMBER,
static_ntab     	static_ntabtyp,
motion_ntab     	motion_ntabtyp

);
/

CREATE TABLE deposit_objtab OF deposit_objtyp( CONSTRAINT deposit_pk PRIMARY KEY(id)
)
NESTED TABLE static_ntab STORE AS static_store_ntab, NESTED TABLE motion_ntab STORE AS motion_store_ntab ;

Export:

Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ... Current user changed to SYS

. . exporting table     DEPOSIT_OBJTAB            10 rows exported
. . exporting table  	STATIC_STORE_NTAB          1 rows exported

Export terminated successfully without warnings.

IMPORT:
I've tried many variations on this theme:

imp sys/ file=deposit.dmp fromuser=TARGET ROWS=Y ignore=Y toid_novalidate=(target.deposit_form_objtab, target.deposit_objtyp)

.....
Export file created by EXPORT:V08.01.07 via conventional path import done in US7ASCII character set and US7ASCII NCHAR character set

. importing SYS's objects into SYS
. importing SYS's objects into TARGET
. . skipping TOID validation on type DEPOSIT_OBJTYP

IMP-00061: Warning: Object type "DDEPOSIT_OBJTYP" already exists with a different identifier
"CREATE TYPE "DEPOSIT_OBJTYP" TIMESTAMP '2001-10-31:14:24:08' OID
'91A47"
"9F7E5224DB3E034080020D0F326' AS OBJECT("
"id NUMBER,"
"static_ntab static_ntabtyp"
");"

IMP-00063: Warning: Skipping table "DEPOSIT_OBJTAB" because object type "DEPOSIT_OBJTYP" cannot be created or has different identifier IMP-00086: TOID "DEPOSIT_OBJTAB" not found in export file Import terminated successfully with warnings.

Thank you for any information which would be of help. Received on Wed Dec 05 2001 - 07:14:01 CST