ORA-27040: file create error, unable to create file

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

It's not the first time i write about the stack of errors returned by data pump operations and ORA-31641 in particular, however the error messages may vary slightly with the operating system. For instance the following error stack is returned on XE running on top of Ubuntu, whereas the previously linked page refers to an error condition occurring on XE for Windows.

expdp user/pwd directory=EXPDIR dumpfile=dump.dp tables=emp,dept nologfile=Y

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "/home/flavio/documents/dump.dp"
ORA-27040: file create error, unable to create file
Linux Error: 13: Permission denied

The problem is caused by the missing privileges of user oracle on the directory pointed to by EXPDIR.
EXPDIR is pointing to /home/flavio/documents.

ls -ld /home/flavio/documents

drwxr-xr-x 4 flavio flavio 4096 2009-04-10 19:00 /home/flavio/documents/

As you see the directory belongs to user flavio, group flavio, moreover no write privileges are granted other than to the directory owner. Now, as Oracle processes are running under the user oracle, in order to be able to dump a file in this directory we have two possibilities:

1. to change the other group permissions on the directory i.e. by executing chmod o+w documents.
   
2. to add user oracle to group flavio and grant write permissions on documents to group flavio by executing sudo usermod -a -G flavio oracle; chmod g+w documents.

Note that option #1 will work instantly while option #2 requires a db restart. If you do not restart the db, you will still get the permission denied error. In either case, the db user must have READ and WRITE permissions on EXPDIR or hold CREATE ANY DIRECTORY privilege.

Whether the optimal solution is either of the two options above it really depends on the application security requirements.
In the end may be it's better to create a public directory where everybody can dump files freely or create a shared directory EXPDIR where only oracle can create files and users belonging to a datapump group can read them. This approach avoids tampering with the existing permissions on user's private folders and it doesn't require a db restart.

Final notes: while looking for the Optimal Flexible Architecture document, i realized that dpdump is nowhere to be found in the installation documents through version 11gR1. This directory is created at time of installation and it is an OFA directory, no matter what the books say or don't. Directory object DATA_PUMP_DIR, which is the default directory for data pump operations, indeed maps to dpdump.
If i remember correctly in 10gR2 the directory object is automatically created at installation time whilst in 10gR1 you need to create it manually.

See message translations for ORA-27040 and search additional resources.

ORA-31641: unable to create dump file

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

You are getting this error while performing a data pump export (EXPDP) using a dump filename that is already present at the destination.

EXPDP 'user/pwd' SCHEMAS=user DUMPFILE=dumpfile.dmp NOLOGFILE=Y DIRECTORY=expdir

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file "C:\oracle\product\10.1.0\admin\orcl10\dpdump\dumpfile.dmp"
ORA-27038: created file already exists
OSD-04010: option specified, file already exists

Dump files are never overwritten (in contrast with LOG files and SQL files generated by the data pump), so you must either change the dump filename in the command line or rename or move or delete the existing file prior to attempting the operation again.

See a previous posting for other common error situations that you can encounter when performing a data pump export.

See message translations for ORA-31641 and search additional resources

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.