Problem when importing Oracle 9i dmp file into Oracle 10g
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

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

Hybrid View

  1. #1
    Join Date
    Jun 2005
    Location
    Ottawa, Ontario, Canada
    Posts
    18

    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

  2. #2
    Join Date
    Jun 2005
    Posts
    31

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

    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,

  3. #3
    Join Date
    Jun 2005
    Location
    Ottawa, Ontario, Canada
    Posts
    18
    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

  4. #4
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Create all the tablespaces on the target before performing the import.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  5. #5
    Join Date
    Jun 2005
    Posts
    31
    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,

  6. #6
    Join Date
    Jun 2005
    Location
    Ottawa, Ontario, Canada
    Posts
    18
    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,

  7. #7
    Join Date
    Jan 2001
    Posts
    3,131
    Can you post the error log, or is it the same?
    I remember when this place was cool.

  8. #8
    Join Date
    Jun 2005
    Location
    Ottawa, Ontario, Canada
    Posts
    18
    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?

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width