ORA-39087: directory name DATAPUMPDIR is invalid

If you get this error there are a few possible issues.

DIRECTORY=DATAPUMPDIR
DUMPFILE=database_name_EXP_03062009.DMP
LOGFILE=database_name.LOG
SCHEMAS=name_of_schema
TABLE_EXISTS_ACTION=REPLACE

The directory you are calling (DATAPUMPDIR) does not exist. We can check that by using the following query.

SELECT * FROM all_directories;

view_all_directories

Looking at the results we can see the default directory for datapump was set during install to the following:
D:\ora_app\oracle\admin\database_name\dpdump\ and the alias is DATA_PUMP_DIR. So if I had used DATA_PUMP_DIR instead of DATAPUMPDIR I would not have see an error.

Since I like having things where I can find them quickly (instead of having to navigate several levels deep into a directory structure) I used the following code to create a new directory in the database. After creating the folder directly on the system first.

CREATE DIRECTORY DATAPUMPDIR as ‘C:_DATAPUMDIR’;

Then we need to grant access to the user (me) or we will need to log in as the SYS user because of the permissions on the folder and the permissions within Oracle. The user must have the permissions/privillages to IMPORT/EXPORT databases.

GRANT READ, WRITE on DIRECTORY DATAPUMPDIR to AJ_MENDO;

Using the following query we can see the access of the user.

SELECT *
FROM user_tab_privs
WHERE table_name = ‘DATAPUMPDIR’;

As we can see I now have access to use this directory.

directory_access

Then all you do is execute your export utility created in the previous post.
https://sqlaj.wordpress.com/2009/11/13/import-export-using-data-pump/

Let me know if you have questions.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s