ORA-39001, ORA-39000, ORA-39087, ORA-31631 and the missing directory privileges

As i explained in my previous posting, after fixing the quota problem, you can still hit a few problems:

>expdp yocoya/yocoya@xe tables=(items,images) dumpfile=exp_dir:testdp.dmp nologfile=Y

Export: Release 10.2.0.1.0 - Production on Thursday, 31 May, 2007 10:06:24

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39087: directory name EXP_DIR is invalid

Oracle is complaining about EXP_DIR, but if we check ALL_DIRECTORIES:

select * from all_directories;

OWNER  DIRECTORY_NAME  DIRECTORY_PATH
-----  --------------  --------------
SYS    EXP_DIR         C:\Temp

We see that the directory is existing and (i guess) points to a valid path.

This means that most likely we forgot to grant the WRITE privilege on that directory to the user doing the export. Before granting (or asking for) the privilege, you can find out if you have been granted the privilege in this fashion:

select * from user_tab_privs
where table_name = 'EXP_DIR';

no rows selected

So let's try and see what we get:

grant write on directory exp_dir to yocoya;

>expdp yocoya/yocoya@xe tables=(items,images) dumpfile=exp_dir:testdp.dmp nologfile=Y

Export: Release 10.2.0.1.0 - Production on Thursday, 31 May, 2007 11:10:53

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31631: privileges are required

With some surprise we see that Oracle this time is complaining about missing privileges just after that we granted the write permission!
Interestingly enough, it means that the READ privilege is necessary too, not just WRITE.
Let's do it:

grant read on directory exp_dir to yocoya;

Now, unless you forgot to delete a previous dump with the same name, you should get the export dump, finally!


PS: if you specified an existing name for the dump file, you will see the following error:

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "H:\Documents\Apex\oracletext\testdp.dmp"
ORA-27038: created file already exists
OSD-04010: opci┐CREATE> especificada; el archivo ya existe

This is another difference between EXP and EXPDP, because EXP overwrites the export file without warnings.

Funnily enough i got the last message in spanish, the primary installation language i guess, even if i had...

set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

There must be some quirk in some DLL, but don't count on me to find out which one it is!

¡Que viva Mexico!

See message translations for ORA-39001, ORA-39000, ORA-39087, ORA-31631 and search additional resources.