Friday, November 18, 2016

ORA-39087 - How to Create a DATA_PUMP directory on Oracle to import a DMP on custom DATA_PUMP Location.

When running an import a directory needs to be set in the impdp command as follows:

impdp schemas=MERCH directory=DATA_PUMP dumpfile=MERCH.DMP logfile=impdp_merch.log

If you send a directory that actaully exist on your system, Oracle still will complain about it.

impdp schemas=MERCH directory=/media/sf_shared dumpfile=MERCH.DMP logfile=impdp_merch.log

ORA-39087: directory name /MEDIA/SF_SHARED is invalid

As you can see oralce sees my directory on UPPER case, so I needed to make sure that /MEDIA/SF_SHARED existed on my system, so I created a symbolic link to make it work.

But the issue was still present:

ORA-39087: directory name /MEDIA/SF_SHARED is invalid

So, I found that 3 things need to happen:

1.- directory must exist on your system, and must have read and write privileges
2.- directory must exist with UPPER case since oralce impdp will require it like that
3.- Database needs to be aware of that directory so you need to create it as follows:

after the 3 conditions are met, you can do this:

impdp schemas=MERCH directory=DATA_PUMP_SHARED dumpfile=MERCH.DMP logfile=impdp_merch.log

And then the import will work without the ORA-39087 error, I hope this is helpful for you :D

UPDATE (2/16/2017):
I have learn that the directory that is needed into the impdp, needs to live in dba_directories table , with the reference to the path you are trying to set, so instead of doing upper case as described above just make sure you use the correct Directory_name

Special thanks to this guy:

Post a Comment