Error
Error Code: ORA-29280

Oracle Error ORA-29280: Invalid Directory

📦 Oracle Database
📋

Description

The ORA-29280 error indicates that the directory object specified in your PL/SQL code does not exist in the Oracle database. This error commonly occurs when attempting file operations (e.g., reading or writing files) using the `UTL_FILE` package.
💬

Error Message

ORA-29280: invalid directory object
🔍

Known Causes

4 known causes
⚠️
Directory Object Missing
The specified directory object was never created in the database. It needs to be created using the `CREATE DIRECTORY` command.
⚠️
Typographical Error
There is a typo in the directory object name within the PL/SQL code. This can lead to the database not recognizing the directory.
⚠️
Insufficient Privileges
The user executing the PL/SQL code lacks the necessary privileges to access the specified directory object. The user needs READ or WRITE privileges on the directory.
⚠️
Directory Object Dropped
The specified directory object was previously dropped from the database, and the PL/SQL code was not updated.
🛠️

Solutions

3 solutions available

1. Verify Directory Object Existence and Permissions easy

Ensure the Oracle DIRECTORY object exists and has the correct permissions for the user trying to access it.

1
Log in to SQL*Plus or SQL Developer as a user with DBA privileges (e.g., SYS, SYSTEM) or a user who has been granted `CREATE ANY DIRECTORY` or `CREATE DIRECTORY` privileges.
2
Check if the directory object exists. Replace `your_directory_name` with the actual name of the directory object you are trying to use.
SELECT directory_name, directory_path FROM dba_directories WHERE directory_name = 'YOUR_DIRECTORY_NAME';
3
If the directory object does not exist, create it. Replace `your_directory_name` and `/path/to/your/directory` with the appropriate values. Ensure the Oracle OS user has read/write permissions on this OS directory.
CREATE OR REPLACE DIRECTORY your_directory_name AS '/path/to/your/directory';
4
Grant the necessary privileges to the user or role that needs to access the directory. Replace `user_or_role_name` and `your_directory_name`.
GRANT READ, WRITE ON DIRECTORY your_directory_name TO user_or_role_name;
5
If you are running a PL/SQL block or procedure, ensure the user executing it has been granted these privileges.

2. Confirm OS Directory Path and Oracle User Permissions medium

Verify that the operating system directory specified in the Oracle DIRECTORY object actually exists and that the Oracle database OS user has the correct read/write permissions.

1
Identify the OS path associated with the Oracle DIRECTORY object. You can find this using the SQL query from Solution 1, Step 2.
SELECT directory_path FROM dba_directories WHERE directory_name = 'YOUR_DIRECTORY_NAME';
2
Log in to the Oracle database server's operating system as a user with sufficient privileges (e.g., root or the Oracle OS user).
3
Check if the OS directory exists. Replace `/path/to/your/directory` with the actual path obtained in Step 1.
ls -ld /path/to/your/directory
4
If the directory does not exist, create it. Ensure you have the necessary OS privileges.
mkdir -p /path/to/your/directory
5
Determine the Oracle OS user. This is typically the user that owns the Oracle database installation (e.g., 'oracle'). You can often find this by checking the owner of the Oracle binaries or by looking at the ownership of the Oracle inventory.
ps -ef | grep pmon
6
Grant read and write permissions to the Oracle OS user on the OS directory. Replace `oracle` with the actual Oracle OS user and `/path/to/your/directory` with the directory path.
sudo chown -R oracle:oinstall /path/to/your/directory
sudo chmod -R 755 /path/to/your/directory
7
If the directory is on a shared filesystem or network mount, ensure the Oracle OS user has permissions to access it, and that the mount options allow for read/write operations.

3. Recreate Directory Object and Reapply Grants medium

If the directory object is corrupted or its configuration is suspect, dropping and recreating it can resolve the issue.

1
Log in to SQL*Plus or SQL Developer with DBA privileges.
2
Backup any existing grants associated with the directory object. Replace `your_directory_name`.
SELECT grantee, privilege FROM dba_tab_privs WHERE table_name = 'YOUR_DIRECTORY_NAME';
3
Drop the existing directory object. Replace `your_directory_name`.
DROP DIRECTORY your_directory_name;
4
Recreate the directory object with the correct OS path. Replace `your_directory_name` and `/path/to/your/directory`.
CREATE OR REPLACE DIRECTORY your_directory_name AS '/path/to/your/directory';
5
Reapply the necessary grants to users or roles that need access. Use the information gathered in Step 2. Replace `user_or_role_name` and `your_directory_name`.
GRANT READ, WRITE ON DIRECTORY your_directory_name TO user_or_role_name;