ADVERTISEMENT

Create Directory

Directory objects in Oracle map to physical server paths and are required for file operations like expdp / impdp.

1. Create Physical Directory on Server

Run on: DB server as oracle user
Use case: Create a location to store dump/log files.

# Create physical path
mkdir -p /u01/dpump_dir

# Set proper ownership and permissions
chown oracle:oinstall /u01/dpump_dir
chmod 755 /u01/dpump_dir

Note: -p = create parent directories automatically if they don’t exist.

2. Create Directory Object in Database

Run in: SQL*Plus as SYSDBA or privileged user
Use case: Map DB object to OS directory for Data Pump.

-- Create DB directory object
CREATE DIRECTORY DPUMP_DIR AS '/u01/dpump_dir';

3. Grant Permissions to User

Run in: SQL*Plus
Use case: Allow user to access directory during export/import.

-- Grant directory access to Data Pump user
GRANT READ, WRITE ON DIRECTORY DPUMP_DIR TO hr;

You can also grant to multiple users:

GRANT READ, WRITE ON DIRECTORY DPUMP_DIR TO SYS, PUBLIC;

📌 Notes:

  • Directory names in Oracle are case-insensitive, but by convention, keep them UPPERCASE for clarity.
  • The directory path must be on the database server, not on the client machine.
  • Users need two types of permissions to use a directory object successfully:
    • Database permission to access the directory object
    • OS-level read/write permission on the actual filesystem directory
  • Common naming conventions: use names like DPDIR, EXP_DIR, or DPUMP_LOGS to keep directory objects consistent and recognizable.

ADVERTISEMENT