Thursday, May 31, 2007

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.

2 comments:

Anonymous said...

Nice web site. I've noticed in Oracle Database 11g Enterprise Edition Release 11.1.0.6.0, that expdp'd files with '_' in the filename throw a "ORA-39001: invalid argument value" too. Alter the filename and you're able to import. Just thought I'd throw that in there.

Byte64 said...

that's curious, i've just tried on Oracle XE on linux and it crunches the file without problems.

does return any other message in the error stack besides ORA-39001?

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles