Oracle Directory

by

Data Pump Error – ORA-39070: Unable to open the log file

 had a user today who really could not grasp the an error when trying to do an export with expdp
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 536
ORA-29283: invalid file operation
a quick check to see what the user had defined so far:
SQL> select directory_name, directory_path from dba_directories
DIRECTORY_NAME     DIRECTORY_PATH
——————————————————————————–
DBBACKUPS       e:\dbbackups
two things she hadn’t done:
  1. given correct permissions for that user to acccess the logical directory for DBACKUPS:
SQL> GRANT read, WRITE ON DIRECTORY DBACKUPS TO [username];

2. create the physical directory

cmd> mkdir e:\dbbackups
by

Oracle Directory

 

An oracle directory is a db resident object which points to a location on the network which is visible to the database server.

Create a database directory:

syntax : CREATE OR REPLACE DIRECTORY [logical directory_name] AS [physical DIRECTORY path];

Windows
SQL> CREATE OR REPLACE DIRECTORY dumps AS 'c:\dumps';

Linux
SQL> CREATE OR REPLACE DIRECTORY dumps AS '/dumps';

remember if the directory is to be used by other users outside of the creating user, read and/or write access need to be granted

SQL> GRANT read, WRITE ON DIRECTORY dumps TO scott;

Note – it doesn’t allow sub-directory traversal. So a specific directory needs to be created for each physical directory you wish to use