Export/Import DataPump Parameter DIRECTORY - How to Specify a Directory

Export/Import DataPump Parameter DIRECTORY - How to Specify a Directory

A directory can be specified that is used by the Oracle Export/Import DataPump utilities to write the dumpfile(s), logfile (if specified) and SQL file (if specified).

Server-Based versus Client-Based.

The parameter DIRECTORY specifies the location to which Export DataPump or Import DataPump can write the dump file set, the log file, and the SQL file (Import DataPump only).
As export DataPump and import DataPump are server-based, rather than client-based, the output files are accessed relative to server-based directory paths. Data Pump requires you to specify directory paths as directory objects. A directory object maps a name to a directory path on the file system.

To create a directory, you must have the DBA role or you must have been granted the CREATE ANY DIRECTORY privilege.

Example (a DBA creates directories on the Windows platform and grants access to user scott):

CONNECT system/manager
CREATE OR REPLACE DIRECTORY my_dir as 'D:\DataPump';
CREATE OR REPLACE DIRECTORY my_logdir as 'E:\logs';
GRANT read, write ON DIRECTORY my_dir TO scott;
GRANT read, write ON DIRECTORY my_logdir TO scott;

Example (a normal user with the CREATE ANY DIRECTORY privilege creates directories on the Unix platform - this user automatically has READ and WRITE privilege on that directory):

CONNECT system/manager
GRANT CREATE ANY DIRECTORY TO scott;
CONNECT scott/tiger
CREATE OR REPLACE DIRECTORY my_dir as '/usr/DataPump';
CREATE OR REPLACE DIRECTORY my_logdir as '/usr/logs';

Note that the CREATE DIRECTORY statement does not actually create the directory for you on disk. If the directory is invalid, a DataPump job will fail with:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

To query on which directories you have privileges to read and write:

SET lines 80
COL grantee FORMAT a20
COL privilege FORMAT a10
SELECT directory_name, grantee, privilege FROM user_tab_privs t, all_directories d WHERE t.table_name(+)=d.directory_name ORDER BY 1,2;