Problem when importing Oracle 9i dmp file into Oracle 10g

Hello all,

I am encountering the following errors when attempting to export, via Oracle 10g, a dumped Oracle 9i database. The character set of the dumped 9i instance is indeed WE8ISO8859P15, BTW :

Import: Release 10.1.0.2.0 - Production on Wed Jun 15 16:43:49 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Username:
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P15 character set (possible charset conversion)
IMP-00015: following statement failed because the object already exists:
"CREATE UNDO TABLESPACE "UNDOTBS1" BLOCKSIZE 8192 DATAFILE 'E:\ORACLE\ORADA"
"TA\ORCL15\UNDOTBS01.DBF' SIZE 209715200 REUSE AUTOEXTEND ON NEXT 5242880 M"
"AXSIZE 32767M EXTENT MANAGEMENT LOCAL "
IMP-00015: following statement failed because the object already exists:
"CREATE TEMPORARY TABLESPACE "TEMP" BLOCKSIZE 8192 TEMPFILE 'E:\ORACLE\ORAD"
"ATA\ORCL15\TEMP01.DBF' SIZE 105906176 REUSE AUTOEXTEND ON NEXT 655360 MAXS"
"IZE 32767M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1048576"
IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "CWMLITE" BLOCKSIZE 8192 DATAFILE 'E:\ORACLE\ORADATA\ORC"
"L15\CWMLITE01.DBF' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 3"
"2767M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING SE"
"GMENT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file 'E:\ORACLE\ORADATA\ORCL15\CWMLITE01.DBF'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.


etc, etc., and then of course everything else depending on these tablespaces fails also ...

Can anyone shed any light on this ORA-01119 error the keeps on preventing the necessary datafiles from being created ??


Thanks,

Travis Weir

Quote:

---

Originally posted by jimbobfurley

IMP-00017: following statement failed with ORACLE error 1119:
"CREATE TABLESPACE "CWMLITE" BLOCKSIZE 8192 DATAFILE 'E:\ORACLE\ORADATA\ORC"
"L15\CWMLITE01.DBF' SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 3"
"2767M EXTENT MANAGEMENT LOCAL AUTOALLOCATE ONLINE PERMANENT NOLOGGING SE"
"GMENT SPACE MANAGEMENT AUTO"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file 'E:\ORACLE\ORADATA\ORCL15\CWMLITE01.DBF'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

Can anyone shed any light on this ORA-01119 error the keeps on preventing the necessary datafiles from being created ??

---

The ORA-01119 is the follow-up result of

O/S-Error: (OS 3) The system cannot find the path specified.

Do you have this directory and has Oracle permissions to create files there in E:\ORACLE\ORADATA\ORCL15\ ?

Good Luck,

Holy crap ... I completely missed that basic fact ... By reading your post, I just realized that this path (E:\ORACLE\ORADATA\ORCL15\), doesn't exist on my Oracle 10g server machine because there no E: drive. But, the Oracle 9i server (different box entirely) that this dump file was initially exported on is installed on E: ...
Now the question is, how do I take this dump file and import it to a D: path on my Oracle 10g target environment ?



Thanks again for you help,

Travis Weir

Create all the tablespaces on the target before performing the import.

Just create all tablespaces manually before you start the import.
Then you will get the error "cannot create tablespace .." - but it will create the tables and import the data.

I assume that you don't have too many tablespaces and you know them.

Otherwise you can try following:

imp username/pw file=youfile.dmp indexfile=create_commands.txt


==> this will create an ASCII-file containing all "create table" and "create index" (and I hope also "create tablespace") commands, but no data.

Good Luck,

Creating all of the tablespaces manually, via both of these methods, did not resolve the errors. The Oracle 10g Import is still trying to import to the non-existent E: path ... Is there not a way, perhaps with imp.exe, to explicitly tell the dmp file WHERE to dump to ?


Thanks again for your help,

Travis Weir

Quote:

---

Originally Posted by Telco_DBA

Just create all tablespaces manually before you start the import.
Then you will get the error "cannot create tablespace .." - but it will create the tables and import the data.

I assume that you don't have too many tablespaces and you know them.

Otherwise you can try following:

imp username/pw file=youfile.dmp indexfile=create_commands.txt


==> this will create an ASCII-file containing all "create table" and "create index" (and I hope also "create tablespace") commands, but no data.

Good Luck,

---

Can you post the error log, or is it the same?

The error log trace in question, has not changed.

thanks,
travis

Quote:

---

Originally Posted by Mr.Hanky

Can you post the error log, or is it the same?

---

Quote:

---

Originally Posted by jimbobfurley

The error log trace in question, has not changed.

thanks,
travis

---

so you precreated all the tablespaces - did you grant quotas to the users to creat objects?

The errors about trying to create will still be there, but the data should still go in