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-39145: directory object parameter must be specified and non-null

If you, like me, tend to forget the varied syntax of the myriad of oracle commands that have been introduced over the years, you won't be surprised to get errors like that i am going to describe.

EXPDP is the data pump export introduced with Oracle version 10 and it comes with a somewhat funny syntax for what concerns the specification of the export folder, because, unless you are doing the export as a privileged user, a user presumably holding EXP_FULL_DATABASE role like the DBA, it expects that you specify the oracle directory object name along with the name of the file dump. See the linked document for additional possibilities.

This is why when i submitted the following command:

expdp schemas=yocoya dumpfile=testdp.dmp nologfile=Y

i got this verbose error message in return:

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39145: directory object parameter must be specified and non-null

Supposing you have defined a directory object called EXP_DIR, then the correct form of the command is the following:

expdp schemas=yocoya dumpfile=exp_dir:testdp.dmp nologfile=Y

or

expdp schemas=yocoya directory=exp_dir dumpfile=testdp.dmp nologfile=Y

As usual, the object name is case insensitive unless you have explicitly created it enclosed in double quotes. See also the related article about errors caused by the lack of read and write privileges on the destination folder or when an existing file is found.

See error translations for ORA-39145 and search additional resources.

---------------------------------------
ORA-39145: il parametro oggetto directory deve essere specificato e diverso da null
ORA-39145: se debe especificar el parámetro de objeto de directorio y no ser nulo
ORA-39145: el paràmetre d''objecte de directori s''ha d''especificar i no pot ser nul
ORA-39145: le paramètre d'objet de répertoire doit être indiqué et il ne doit pas avoir la valeur Null
ORA-39145: Verzeichnisobjektparameter muss angegeben werden und darf nicht Null sein.
ORA-39145: η παράμετρος αντικειμένου καταλόγου πρέπει να προσδιοριστεί με τιμή διαφορετική της τιμής null
ORA-39145: katalogobjektparameter skal være angivet og ikke-NULL
ORA-39145: katalogobjektparametern måste anges och får inte vara null
ORA-39145: katalogobjektparameteren må være angitt og ikke være null
ORA-39145: hakemisto-objektiparametri on määritettävä, ja se ei saa olla tyhjä
ORA-39145: a könyvtárobjektum paramétert meg kell adni, nem lehet üres
ORA-39145: parametrul pentru obiectul director trebuie să fie specificat şi nenul
ORA-39145: een directoryobjectparameter moet worden opgegeven die niet NULL mag zijn
ORA-39145: o parâmetro de objeto de diretório deve ser especificado e não-nulo
ORA-39145: é necessário que o parâmetro do objecto do directório seja especificado e não seja nulo
ORA-39145: требуется задать параметр 'объект каталога' с определенным значением
ORA-39145: parametr objektu adresáře musí být zadán a nesmí být null
ORA-39145: parameter objektu adresára musí byť zadaný a nesmie mať hodnotu null
ORA-39145: parametr obiektu-katalogu musi być podany i nie może mieć wartości Null
ORA-39145: dizin nesnesi parametresi belirtilmeli ve null olmamalıdır
ORA-39145: directory object parameter must be specified and non-null

See message translations for ORA-39145 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.