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

[jimbobfurley]

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

[Telco_DBA]

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,

[jimbobfurley]

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

[adewri]

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

[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,

[jimbobfurley]

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

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,

[Mr.Hanky]

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

[jimbobfurley]

The error log trace in question, has not changed.

thanks,
travis

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

[davey23uk]

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