We, at our company, have been using a small hack on Oracle Directories names since some time ago.

Before Oracle Directories we have to explicitly set the directory on pfile, by the utl_file_dir parameter. To add a new directory we had to reboot the instance.

After Oracle Directories, everything become easier!

But some legacy scripts still have been using the old style, using utl_File.Fopen('/path',...) instead of utl_File.Fopen('DIRECTORY',...)

The small hack is create a directory in which its name is the exactly path of the desired directory. In a simple example:

create or replace directory "/tmp" as '/tmp';

select * from dba_directories ;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ---------------------
SYS                            /tmp                           /tmp

So, the users can normally use a command like that:

l_file_handle := UTL_FILE.FOPEN('/tmp','file.txt','W');

That's really nice.

But I'm facing a problem when using such a directory as the output of a EXPDP dump. EXPDP parser seems to ignore any quotations marks, and is parsing the directory name in uppercase.

$expdp \"/ as sysdba\" parfile= parfile.par

Export: Release - 64bit Production on Thursday, 05 April, 2018 10:50:19

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

Connected to: Oracle Database 10g Enterprise Edition Release - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name /TMP is invalid

[oracle@brux0009 P00OMS /EXPORT/spool]$ cat parfile.par

Any tips?

  • I assume your export DIRECTORY parameter is somehow influenced by your old legacy scripts. (If not, you can just create another Oracle directory with the same path and a former name. Export/Import data pump only use this parameter to locate where to read/write the dump and log files). However, as Balaz suggested, you can use escaped double quotes to avoid the uppercase parsing.
    – vegatripy
    Commented Apr 5, 2018 at 15:51
  • I would add that the best solution would be to start migrating away from the hack. Start fixing the code to not rely on a directory name object that requires special handling. It doesn't have to be done all at once.
    – EdStevens
    Commented Apr 5, 2018 at 21:11

1 Answer 1


Just because you are able to do something, it does not necessarily mean it is a good idea.

[oracle@o73 ~]$ sqlplus / as sysdba

SQL*Plus: Release Production on Thu Apr 5 16:20:15 2018

Copyright (c) 1982, 2017, Oracle.  All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release - Production

SQL> create directory "/home/oracle" as '/home/oracle';

Directory created.

SQL> Disconnected from Oracle Database 18c Enterprise Edition Release - Production
[oracle@o73 ~]$ expdp \'/ as sysdba\' directory=\\\"/home/oracle\\\" schemas=bp

Export: Release - Production on Thu Apr 5 16:20:33 2018

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_04":  "/******** AS SYSDBA" directory=\"/home/oracle\" schemas=bp
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Master table "SYS"."SYS_EXPORT_SCHEMA_04" successfully loaded/unloaded
Dump file set for SYS.SYS_EXPORT_SCHEMA_04 is:
Job "SYS"."SYS_EXPORT_SCHEMA_04" successfully completed at Thu Apr 5 16:20:53 2018 elapsed 0 00:00:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.