failure on import to new DDL for object table 
Author Message
 failure on import to new DDL for object table

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.



Sun, 23 May 2004 21:14:01 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. CREATE TABLE or Import results in new table being flagged as SYSTEM table

2. Createing new tables with DDL

3. Table disappear when using DTS to import/export database objects

4. DDL import question

5. Format export/import DDL output

6. Generate DB2 import/export ddl

7. create new tables dynamically while importing from excel

8. Import date in format Txt into new table

9. Importing text file w/ODBC in VC5 as new table

10. Import with new table & index extent sizes

11. Stored procedure or SQL to create DDL for objects

12. GetScript.js - dump out the DDL for an object to the console


 
Powered by phpBB® Forum Software