4

I am trying to import data from a dump file created by Oracle 10g data pump utility. The command that I am issuing is

impdp \"username/password@DB as sysdba\" remap_schema=SRC_SCHEMA:TARGET_SCHEMA remap_tablespace=source_tablespace:target_tablespace DUMPFILE=db.dmp

I am getting the following error message:

ORA - 39001: Invalid argument value
ORA - 39000: Bad dump file spcification
ORA - 39088: file name cannot contain a path specification

What is the cause of this error?

Hasan Fathi
  • 5,610
  • 4
  • 42
  • 60
user170008
  • 1,046
  • 6
  • 17
  • 31

1 Answers1

12

From the documentation:

ORA-39088: file name cannot contain a path specification
Cause: The name of a dump file, log file, or sql file contains a path specification.
Action: Use the name of a directory object to indicate where the file should be stored.

This suggests that the parameter you've shown as DUMPFILE=db.dmp is really something like DUMPFILE=C:\some\dir\path\db.dmp, which is not allowed. You have to use a directory that is recognised by the database and specify it with a DIRECTORYparameter.

As @ruffin notes from that directory parameter link, you can put the dump file in the default DATA_PUMP_DIR directory, which you can find from the dba_directories view or, if you have permission to use that object, the all_directories view. The user you're importing as has to have been granted read and write privileges on that for you to be able to use it. You also need to be able to move your dump file into the operating-system directory, so permissions may be an issue there too.

If you don't have a suitable directory object that you have database privileges for and operating-system access to, you'll need to create one and grant suitable privileges. This needs to be done by someone with the appropriate privileges, usually as SYS:

create directory my_data_pump_dir as 'C:\some\dir\path';
grant read, write on directory my_data_pump_dir to <username>;

Then the import is modified to have:

... DUMPFILE=db.dmp DIRECTORY=my_data_pump_dir

Note that the operating system directory has to be available to the Oracle user account (whoever is running the database processes, pmon etc.) on the database server. You cannot import to a remote database using a local file, unless the local directory is somehow mounted on the remote server. The old imp command was a client-side application that often ran on the server but didn't have to; impdp is a server-side application.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • That `DIRECTORY` param page says, _The directory_object is the name of a database directory object (not the file path of an actual directory). Upon installation, privileged users have access to a default directory object named DATA_PUMP_DIR_, which isn't the most helpful if I just want to load a dmp file and do some forensics. Is there a better answer than just stick the dmp file into the [appropriate `dpdump` folder](http://stackoverflow.com/questions/6463614/how-to-import-an-oracle-database-from-dmp-file-and-log-file)? That is, how _do_ I point to C:\myDir? – ruffin Oct 02 '12 at 17:43
  • @ruffin - as the DBA you'd need to [create a directory](http://docs.oracle.com/cd/E14072_01/server.112/e10592/statements_5007.htm) referring to that O/S location, and grant permissions on that new directory object to whoever you're running the `impdp` as. And hopefully you're aware the O/S directory has to be on the DB server, not the client machine. You cannot specify an O/S dir on the command line; from the link to 'default locations': 'The reason that a directory object is required is to ensure data security and integrity'. But this should be a new question, either here or on [dba.se]. – Alex Poole Oct 02 '12 at 18:07
  • Fair enough -- I figured since we were assuming the OP was trying some fixed path we could continue the reinterpretation & extend it to the answers, so we've got it all in the same place. 2¢, fwiw, etc. I believe app developers (who often get connection strings given to them by dbas and then code away) also commonly get legacy dumps and are expected to do local machine forensics/prototyping more often than dbas expect (I'm on my fourth such request), so it's an interesting question and useful skill. Thanks! – ruffin Oct 03 '12 at 13:13
  • @ruffin - yes, probably more related than I thought on first read, and I was going to say '... if it needs to go into more detail' but ran out of room in the comment, so apologies if it sounded harsher than I meant it to! dba.se is not just for DBAs, confusingly, and I think is a better home for `impdp` questions even if it's dev doing the work, but it's a grey area and opinions vary. – Alex Poole Oct 03 '12 at 13:24