Importing dmp file

Posted on 2007-11-14
Last Modified: 2013-12-19
When trying to import a dmp file I get the following erros and the process stops at the last line posted below and stay stuck there.  Its being stuck for more than a day now.  The error messages are not all strange to me; I have got them before but the import still went through - but now it is stuck!

Any help please!!!  Thank in advance...

Import: Release - Production on Wed Nov 14 11:58:25 2007

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

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

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
IMP-00008: unrecognized statement in the export file:

IMP-00017: following statement failed with ORACLE error 1119:
 "I\INDX01.DBF' SIZE 209715200       AUTOEXTEND ON NEXT 20971520  MAXSIZE 327"
IMP-00003: ORACLE error 1119 encountered
ORA-01119: error in creating database file 'D:\ORACLE\ORADATA\FAMIS8I\INDX01.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.
. importing FAMIS's objects into FAMIS

Question by:tadeu_lbc
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 11

Accepted Solution

Akenathon earned 250 total points
ID: 20286163
The import is trying to create the file in a directory that does not exist:

"'D:\ORACLE\ORADATA\FAMIS8I\" the OS complains with this error:

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

...and the create tablespace fails. But, if you have created the tablespace before importing, everything should be OK.

Going forward, you should pre-create the directory structure where the datafiles will be located if you intend to have your tablespaces created by the import itself
LVL 11

Expert Comment

ID: 20286194
One more thing: If you want to monitor the progress of the operation, you can set the parameter feedback=<number>. Just run "imp help=y" to see every option. While it's running, you can check what it's doing in v$session: STATUS should be 'ACTIVE', and the SQL_HASH_VALUE can be used to go to V$SQL and see the exact command it's stuck in.

Author Comment

ID: 20292974

Based on your comments I am getting the impresion that the .dmp file has the path harcoded in it, is that right?  In other words, if I export the .dmp from one database server source and am trying to import into server destination, then my destination sever needs to be identical to the source as far as the path and the installation of the database is concerned???

Thank you!!
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.


Assisted Solution

sora earned 250 total points
ID: 20299458
yes you are right. if you are trying to do a full db import from server A to server B and the server B has no tablespaces pre-created then the directory structures must match. other option - pre-create the tablespaces in your target/destination server. and use ignore=y in your imp statement on the destination server. then oracle will ignore the tabelspace creation since they already exist

Author Comment

ID: 20300188
Thank you guys for the help.  :)
LVL 11

Expert Comment

ID: 20306131
Sorry I did not see you followup earlier: It's just like sora said, the path is hardcoded at export time, so unless your directories happen to be the same in the target server, you've got to create the tablespaces in advance. Ignore=y is for ignoring object creation errors (tables, views, etc.), I don't think it makes any difference when it comes to tablespaces, but it surely won't hurt :-)

Expert Comment

ID: 20310125
if I am not mistaken Akenathon, if ignore=y is not specified, the imp would throw error that tablespace does not exist and then _abort_. default ignore=N means abort the import if error occurs


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

695 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question