Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Importing dmp file

Posted on 2007-11-14
Medium Priority
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 1000 total points
ID: 20286163
The import is trying to create the file in a directory that does not exist:


...so 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!!
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!


Assisted Solution

sora earned 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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 shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

650 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