ORA-06564: Object Does Not Exist - Fix Directory Errors

ORA-06564: Object Does Not Exist

Error Overview

Error Text: ORA-06564: object string does not exist

The ORA-06564 error occurs when PL/SQL code references a named Oracle directory object, file, or external resource that does not exist in the data dictionary. It is most commonly encountered when using UTL_FILE, DBMS_DATAPUMP, DBMS_XSLPROCESSOR, or DBMS_LOB.LOADFROMFILE with a directory name that has not been created with CREATE DIRECTORY, has been dropped, or when the calling user lacks the necessary READ or WRITE privilege on the directory. The error can also appear in package or object references in some older Oracle versions.

Common Causes

1. Directory Object Not Created

• Code references a directory name that was never created with CREATE DIRECTORY
• Script migrated from another environment where the directory existed
• Directory name hard-coded in PL/SQL without a corresponding CREATE DIRECTORY statement

2. Incorrect Directory Name Case

• Oracle directory names are stored in uppercase by default
• Code passing 'mydir' when the directory was created as MY_DIR (stored as MYDIR)
• Case-sensitive quoted directory name mismatch

3. Missing Privilege on the Directory

• Directory exists but the executing user does not have READ or WRITE privilege
• Grant was made to a role, not directly to the user — UTL_FILE requires direct grants
• Privilege revoked after code was written and tested

4. Directory Object Dropped or Recreated

• DBA dropped the directory object as part of a cleanup
• Directory was recreated with a different name or path
• Automatic environment rebuild (e.g., cloud or containerized DB) did not recreate custom directories

5. DBMS_DATAPUMP or External Table Reference

• Data Pump job references a directory that was removed between job creation and execution
• External table DIRECTORY clause points to a non-existent directory object
• RMAN or Data Guard configuration using a directory object that was dropped

Diagnostic Queries

Check if the Directory Object Exists

-- List all directory objects in the database
SELECT
  owner,
  directory_name,
  directory_path,
  origin_con_id
FROM dba_directories
ORDER BY directory_name;

-- Check a specific directory
SELECT directory_name, directory_path
FROM dba_directories
WHERE directory_name = UPPER('&directory_name');

-- Check directories accessible to current user
SELECT directory_name, directory_path
FROM all_directories
ORDER BY directory_name;

Verify Privileges on a Directory

-- Check grants on a specific directory
SELECT
  grantee,
  owner,
  table_name AS directory_name,
  privilege,
  grantable
FROM dba_tab_privs
WHERE table_name = UPPER('&directory_name')
  AND owner = 'SYS'
ORDER BY grantee, privilege;

-- Check all directory privileges for a user
SELECT
  p.grantee,
  p.table_name AS directory_name,
  p.privilege,
  d.directory_path
FROM dba_tab_privs p
JOIN dba_directories d ON d.directory_name = p.table_name
WHERE p.grantee = UPPER('&username')
ORDER BY p.table_name, p.privilege;

Check External Tables Using a Directory

-- Find external tables referencing a specific directory
SELECT
  owner,
  table_name,
  default_directory_name,
  access_type
FROM dba_external_tables
WHERE default_directory_name = UPPER('&directory_name')
ORDER BY owner, table_name;

-- Find Data Pump jobs referencing directories
SELECT
  owner_name,
  job_name,
  operation,
  job_mode,
  state,
  attached_sessions
FROM dba_datapump_jobs
WHERE state NOT IN ('NOT RUNNING')
ORDER BY owner_name, job_name;

Step-by-Step Resolution

1. Create the Missing Directory Object

-- Create a directory object pointing to an OS path
-- The OS directory must exist and the Oracle OS user must have read/write access
CREATE OR REPLACE DIRECTORY data_dir AS '/u01/oracle/data';

-- Verify creation
SELECT directory_name, directory_path
FROM dba_directories
WHERE directory_name = 'DATA_DIR';

-- Grant privileges to the application user
GRANT READ, WRITE ON DIRECTORY data_dir TO app_user;

-- Grant READ only for import/load use cases
GRANT READ ON DIRECTORY data_dir TO app_user;

-- Verify the grant
SELECT grantee, privilege
FROM dba_tab_privs
WHERE table_name = 'DATA_DIR' AND owner = 'SYS';

2. Fix Case Sensitivity in Code References

Oracle directory names are stored uppercase. Always use uppercase when referencing them:

-- BAD: lowercase directory name will cause ORA-06564
DECLARE
  v_file UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN('data_dir', 'output.txt', 'W');  -- Fails
END;
/

-- GOOD: uppercase directory name
DECLARE
  v_file UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN('DATA_DIR', 'output.txt', 'W');  -- Works
  UTL_FILE.PUT_LINE(v_file, 'Hello World');
  UTL_FILE.FCLOSE(v_file);
END;
/

3. Fix UTL_FILE Usage with Proper Error Handling

-- Robust UTL_FILE procedure with directory validation
CREATE OR REPLACE PROCEDURE write_to_file(
  p_dir      VARCHAR2,
  p_filename VARCHAR2,
  p_content  VARCHAR2
) AS
  v_file    UTL_FILE.FILE_TYPE;
  v_dir_cnt NUMBER;
BEGIN
  -- Validate directory exists before attempting file operation
  SELECT COUNT(*) INTO v_dir_cnt
  FROM all_directories
  WHERE directory_name = UPPER(p_dir);

  IF v_dir_cnt = 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Directory object does not exist: ' || UPPER(p_dir) ||
      '. Run: CREATE OR REPLACE DIRECTORY ' || UPPER(p_dir) ||
      ' AS ''/your/os/path'';');
  END IF;

  v_file := UTL_FILE.FOPEN(UPPER(p_dir), p_filename, 'W', 32767);
  UTL_FILE.PUT_LINE(v_file, p_content);
  UTL_FILE.FCLOSE(v_file);

EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    RAISE_APPLICATION_ERROR(-20002,
      'Invalid path for directory: ' || p_dir);
  WHEN UTL_FILE.INVALID_OPERATION THEN
    RAISE_APPLICATION_ERROR(-20003,
      'Invalid file operation. Check write permissions on: ' || p_dir);
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(v_file) THEN
      UTL_FILE.FCLOSE(v_file);
    END IF;
    RAISE;
END;
/

4. Fix DBMS_DATAPUMP Directory References

-- Check and recreate directory for Data Pump jobs
-- Step 1: Verify directory
SELECT directory_name, directory_path
FROM dba_directories
WHERE directory_name = 'DATA_PUMP_DIR';

-- Step 2: Create or recreate if missing
CREATE OR REPLACE DIRECTORY data_pump_dir AS '/u01/oracle/admin/ORCLCDB/dpdump';

-- Grant to target user
GRANT READ, WRITE ON DIRECTORY data_pump_dir TO datapump_user;

-- Step 3: Verify OS path exists (run from OS shell)
-- ls -la /u01/oracle/admin/ORCLCDB/dpdump/
-- If path does not exist:
-- mkdir -p /u01/oracle/admin/ORCLCDB/dpdump
-- chown oracle:oinstall /u01/oracle/admin/ORCLCDB/dpdump

-- Step 4: Re-run Data Pump export
DECLARE
  v_handle NUMBER;
BEGIN
  v_handle := DBMS_DATAPUMP.OPEN(
    operation  => 'EXPORT',
    job_mode   => 'SCHEMA',
    job_name   => 'EXP_APP_SCHEMA'
  );
  DBMS_DATAPUMP.ADD_FILE(
    handle    => v_handle,
    filename  => 'app_schema.dmp',
    directory => 'DATA_PUMP_DIR'
  );
  DBMS_DATAPUMP.START_JOB(v_handle);
END;
/

5. Fix External Table Directory References

-- Identify external tables with missing directory
SELECT
  t.owner,
  t.table_name,
  et.default_directory_name
FROM dba_tables t
JOIN dba_external_tables et
  ON t.owner = et.owner AND t.table_name = et.table_name
WHERE et.default_directory_name NOT IN (
  SELECT directory_name FROM dba_directories
);

-- Recreate the directory and reassign
CREATE OR REPLACE DIRECTORY ext_data_dir AS '/u01/oracle/external';
GRANT READ ON DIRECTORY ext_data_dir TO app_user;

-- Recreate the external table with the correct directory
ALTER TABLE ext_employees DEFAULT DIRECTORY ext_data_dir;

Prevention Strategies

1. Document All Directory Objects

-- Generate a directory object inventory script
SELECT
  'CREATE OR REPLACE DIRECTORY ' || directory_name ||
  ' AS ''' || directory_path || ''';' AS create_stmt
FROM dba_directories
WHERE directory_name NOT IN ('ORACLE_HOME', 'ORACLE_BASE')
ORDER BY directory_name;

-- Save this as part of your environment rebuild runbook

2. Validate Directories at Startup

-- Procedure to validate all expected directories exist
CREATE OR REPLACE PROCEDURE validate_directories AS
  v_count NUMBER;
  TYPE t_dirs IS TABLE OF VARCHAR2(128);
  v_required_dirs t_dirs := t_dirs(
    'DATA_DIR', 'LOG_DIR', 'ARCHIVE_DIR', 'DATA_PUMP_DIR'
  );
BEGIN
  FOR i IN 1..v_required_dirs.COUNT LOOP
    SELECT COUNT(*) INTO v_count
    FROM dba_directories
    WHERE directory_name = v_required_dirs(i);

    IF v_count = 0 THEN
      DBMS_OUTPUT.PUT_LINE('MISSING DIRECTORY: ' || v_required_dirs(i));
    ELSE
      DBMS_OUTPUT.PUT_LINE('OK: ' || v_required_dirs(i));
    END IF;
  END LOOP;
END;
/

EXEC validate_directories;

3. Include Directory DDL in Version Control

• Store all CREATE DIRECTORY statements in your schema migration scripts
• Include directory creation in environment provisioning automation (Ansible, Terraform)
• Add directory validation to post-deployment smoke test scripts

4. Use Constants for Directory Names

-- Define directory names as package constants to avoid typos
CREATE OR REPLACE PACKAGE app_dirs AS
  c_data_dir    CONSTANT VARCHAR2(128) := 'DATA_DIR';
  c_log_dir     CONSTANT VARCHAR2(128) := 'LOG_DIR';
  c_archive_dir CONSTANT VARCHAR2(128) := 'ARCHIVE_DIR';
END app_dirs;
/

-- Reference in code
v_file := UTL_FILE.FOPEN(app_dirs.c_data_dir, 'output.txt', 'W');

Related Errors

• ORA-29283 - Invalid file operation
• ORA-06512 - At line (PL/SQL error stack)
• ORA-01031 - Insufficient privileges
• ORA-00942 - Table or view does not exist