Oracle import dump file throwing ORA-39088 error

Question

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?

Answer 1

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.