• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1534
  • Last Modified:

Importing dmp file

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 10.1.0.4.2 - 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 10.2.0.1.0 - Produc
tion
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:
 "CREATE TABLESPACE "INDX" BLOCKSIZE 8192 DATAFILE  'D:\ORACLE\ORADATA\FAMIS8"
 "I\INDX01.DBF' SIZE 209715200       AUTOEXTEND ON NEXT 20971520  MAXSIZE 327"
 "67M EXTENT MANAGEMENT LOCAL  AUTOALLOCATE  ONLINE PERMANENT  SEGMENT SPACE "
 "MANAGEMENT AUTO"
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


0
tadeu_lbc
Asked:
tadeu_lbc
  • 3
  • 2
  • 2
2 Solutions
 
AkenathonCommented:
The import is trying to create the file in a directory that does not exist:

"'D:\ORACLE\ORADATA\FAMIS8I\"

...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
0
 
AkenathonCommented:
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.
0
 
tadeu_lbcAuthor Commented:
Akenathon:

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!!
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
soraCommented:
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
0
 
tadeu_lbcAuthor Commented:
Thank you guys for the help.  :)
0
 
AkenathonCommented:
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 :-)
0
 
soraCommented:
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

-sora
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now