ORA-39166: Object Not Found for Export - Fix Data Pump

ORA-39166: Object Was Not Found or Could Not Be Exported

Error Overview

Error Text: ORA-39166: Object OBJECT_TYPE:"SCHEMA"."OBJECT_NAME" was not found or could not be exported.

ORA-39166 occurs during a Data Pump export (expdp) when an object explicitly requested in the job parameters does not exist or is inaccessible to the exporting user. The error appears in the Data Pump log file and causes that specific object to be skipped; the export continues with other objects.

The error is most commonly triggered when using the TABLES=, INCLUDE=, or TABLES=schema.table parameters and the specified objects do not exist, have been dropped, or cannot be accessed by the export user. It also appears when a wildcard or query filter matches no objects.

Common Causes

1. Table or Object Does Not Exist in the Specified Schema

• Typo in the schema name or object name in the TABLES= parameter
• Object was dropped between when the export job was scheduled and when it ran
• Schema name is case-sensitive in the parameter and does not match the stored name (Oracle stores names in uppercase by default)

2. Exporting User Cannot Access the Object

• Export user does not have SELECT privilege on the specified table
• Export user does not have EXP_FULL_DATABASE or DATAPUMP_EXP_FULL_DATABASE role
• Schema-level export requested for a schema the export user cannot read

3. INCLUDE/EXCLUDE Filter Produces No Matches

• INCLUDE=TABLE:"IN ('TABLE_A', 'TABLE_B')" where neither table exists
• INCLUDE=TABLE:"LIKE 'REPORT_%'" where no tables match the pattern in the schema
• Case mismatch in the INCLUDE filter string (Oracle object names are uppercase)

4. Partitioned Table Partition Not Found

• TABLES=schema.table:PARTITION_NAME used but the partition does not exist
• Partition was merged, dropped, or renamed since the export command was written
• Wrong partition name (e.g., SYS_P123 auto-generated names change after partition operations)

5. Object Filtered Out by Oracle Data Pump’s Internal Rules

• Some internal Oracle objects cannot be exported and produce ORA-39166 when explicitly requested
• Object belongs to SYS or SYSTEM and is excluded from user-initiated exports
• Object is a cluster, IOT overflow segment, or other structure not directly exportable

Diagnostic Queries

Verify the Object Exists and Is Accessible

-- Does the specified object exist?
SELECT
  owner,
  object_name,
  object_type,
  status,
  last_ddl_time
FROM dba_objects
WHERE owner = UPPER('&schema_name')
  AND object_name = UPPER('&object_name')
  AND object_type = UPPER('&object_type');

-- If nothing returned, search more broadly:
SELECT owner, object_name, object_type, status
FROM dba_objects
WHERE object_name = UPPER('&object_name')
ORDER BY owner, object_type;

-- Check with case-insensitive search (in case of case-sensitive object creation):
SELECT owner, object_name, object_type
FROM dba_objects
WHERE UPPER(object_name) = UPPER('&object_name')
ORDER BY owner;

Check Export User Privileges

-- Does the export user have EXP_FULL_DATABASE or DATAPUMP_EXP_FULL_DATABASE?
SELECT granted_role
FROM dba_role_privs
WHERE grantee = UPPER('&export_user')
  AND granted_role IN ('EXP_FULL_DATABASE', 'DATAPUMP_EXP_FULL_DATABASE');

-- Can the export user select from the table?
SELECT privilege
FROM dba_tab_privs
WHERE grantee = UPPER('&export_user')
  AND table_name = UPPER('&table_name')
  AND table_schema = UPPER('&schema_name');

-- Export user's system privileges:
SELECT privilege FROM dba_sys_privs
WHERE grantee = UPPER('&export_user')
ORDER BY privilege;

Validate INCLUDE/EXCLUDE Filter Values

-- Check which tables would match an INCLUDE=TABLE filter:
-- If INCLUDE=TABLE:"IN ('ORDERS', 'CUSTOMERS')"
SELECT table_name
FROM dba_tables
WHERE owner = UPPER('&schema_name')
  AND table_name IN ('ORDERS', 'CUSTOMERS');

-- Check which tables match a LIKE pattern:
-- If INCLUDE=TABLE:"LIKE 'REPORT_%'"
SELECT table_name
FROM dba_tables
WHERE owner = UPPER('&schema_name')
  AND table_name LIKE 'REPORT_%'
ORDER BY table_name;

-- All tables in the schema (to verify the target objects exist):
SELECT table_name, num_rows, last_analyzed, status
FROM dba_tables
WHERE owner = UPPER('&schema_name')
ORDER BY table_name;

Check for Partitioned Tables and Their Partitions

-- Verify a specific partition exists:
SELECT
  table_owner,
  table_name,
  partition_name,
  partition_position,
  num_rows,
  last_analyzed,
  high_value
FROM dba_tab_partitions
WHERE table_owner = UPPER('&schema_name')
  AND table_name = UPPER('&table_name')
ORDER BY partition_position;

-- Find partition by approximate date/value if name is unknown:
SELECT partition_name, high_value
FROM dba_tab_partitions
WHERE table_owner = UPPER('&schema_name')
  AND table_name = UPPER('&table_name')
  AND UPPER(high_value) LIKE '%&approx_value%'
ORDER BY partition_position;

Review the Data Pump Job Log for All ORA-39166 Occurrences

# Extract all ORA-39166 errors from the log:
grep "ORA-39166" /path/to/export.log

# Extract the object names that could not be exported:
grep "ORA-39166" /path/to/export.log | sed "s/ORA-39166: Object //"

Step-by-Step Resolution

1. Verify Object Names Are Uppercase in Parameters

Oracle stores object names in uppercase unless they were created with double-quote delimiters. Data Pump parameters are case-sensitive:

# WRONG — lowercase table name:
expdp system/password TABLES=myschema.orders DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp

# CORRECT — uppercase:
expdp system/password TABLES=MYSCHEMA.ORDERS DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp

# Or use quotes with uppercase:
expdp system/password TABLES='"MYSCHEMA"."ORDERS"' DIRECTORY=DATA_PUMP_DIR DUMPFILE=test.dmp

2. Confirm the Object Exists Before Exporting

-- Run this before every targeted export:
SELECT object_name, object_type, status
FROM dba_objects
WHERE owner = 'MYSCHEMA'
  AND object_name IN ('ORDERS', 'CUSTOMERS', 'PRODUCTS')
ORDER BY object_name;
-- Verify all expected objects are returned before running expdp.

3. Grant Required Privileges to the Export User

-- For exporting any schema (full database export capability):
GRANT DATAPUMP_EXP_FULL_DATABASE TO export_user;

-- For exporting a specific schema only:
-- The export user must either own the schema or have SELECT ANY TABLE:
GRANT SELECT ANY TABLE TO export_user;
GRANT SELECT ANY DICTIONARY TO export_user;

-- Or grant object-level access:
GRANT SELECT ON myschema.orders TO export_user;

4. Fix INCLUDE Filter Case and Syntax

# WRONG — lowercase in INCLUDE filter:
expdp system/password \
  SCHEMAS=MYSCHEMA \
  INCLUDE=TABLE:"IN ('orders', 'customers')" \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=export.dmp

# CORRECT — uppercase in INCLUDE filter:
expdp system/password \
  SCHEMAS=MYSCHEMA \
  INCLUDE=TABLE:"IN ('ORDERS', 'CUSTOMERS')" \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=export.dmp

# Verify filter with SQL before running:

SELECT table_name FROM dba_tables
WHERE owner = 'MYSCHEMA'
  AND table_name IN ('ORDERS', 'CUSTOMERS');
-- Must return rows for the export to find the objects.

5. Fix Partition Export When Partition Name Is Wrong

# Verify the partition name first (see diagnostic queries above), then:
expdp system/password \
  TABLES=MYSCHEMA.SALES:SALES_Q1_2024 \  # Use exact partition name from dba_tab_partitions
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=partition_export.dmp

6. Export the Schema Without Specific Object Filters

If the targeted objects cannot be found and you need a complete schema export:

# Remove the problematic TABLES= or INCLUDE= parameter and export the full schema:
expdp system/password \
  SCHEMAS=MYSCHEMA \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=myschema_full.dmp \
  LOGFILE=myschema_full.log

7. Use QUERY Parameter Instead of TABLE Filter for Data Subsets

# Instead of a partition name (which may change), use a QUERY filter:
expdp system/password \
  TABLES=MYSCHEMA.SALES \
  QUERY=MYSCHEMA.SALES:'"WHERE sale_date >= DATE ''2024-01-01'' AND sale_date < DATE ''2024-04-01''"' \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=sales_q1.dmp

8. Handle SYS/SYSTEM Objects That Cannot Be Exported

# If ORA-39166 fires for SYS or internal objects, exclude them:
expdp system/password \
  FULL=Y \
  EXCLUDE=SCHEMA:\"IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP')\" \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=full_export.dmp

Prevention Strategies

1. Validate All Object Names Before Running Scheduled Exports

-- Pre-export validation procedure:
CREATE OR REPLACE PROCEDURE validate_export_objects(
  p_schema IN VARCHAR2,
  p_tables IN SYS.ODCIVARCHAR2LIST  -- List of table names
) AS
BEGIN
  FOR i IN 1..p_tables.COUNT LOOP
    DECLARE
      v_exists NUMBER;
    BEGIN
      SELECT COUNT(*) INTO v_exists
      FROM dba_tables
      WHERE owner = UPPER(p_schema)
        AND table_name = UPPER(p_tables(i));

      IF v_exists = 0 THEN
        DBMS_OUTPUT.PUT_LINE('WARNING: Table not found: ' || p_schema || '.' || p_tables(i));
      ELSE
        DBMS_OUTPUT.PUT_LINE('OK: ' || p_schema || '.' || p_tables(i));
      END IF;
    END;
  END LOOP;
END;
/

-- Usage:
BEGIN
  validate_export_objects(
    'MYSCHEMA',
    SYS.ODCIVARCHAR2LIST('ORDERS', 'CUSTOMERS', 'PRODUCTS')
  );
END;
/

2. Use Schema-Level Exports Instead of Object-Level When Possible

# Schema exports are more resilient — they export whatever exists:
expdp system/password \
  SCHEMAS=MYSCHEMA \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=myschema_%U.dmp \
  PARALLEL=4
# Avoids ORA-39166 from specific table lists that may become stale.

3. Maintain an Export Configuration File With Validation

# Store export parameters in a parfile:
cat > /tmp/export_myschema.par << 'EOF'
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=myschema_%U.dmp
LOGFILE=myschema_export.log
SCHEMAS=MYSCHEMA
PARALLEL=2
EOF

# Always verify the schema and key tables before running:
sqlplus -S system/password <<'SQLEOF'
SELECT 'MYSCHEMA' AS schema_name, COUNT(*) AS table_count FROM dba_tables WHERE owner='MYSCHEMA';
SQLEOF

expdp system/password PARFILE=/tmp/export_myschema.par

4. Log and Alert on ORA-39166 in Automated Jobs

# In monitoring scripts, check for ORA-39166 in export logs:
if grep -q "ORA-39166" /path/to/export.log; then
  echo "WARNING: Some objects could not be exported. Review export.log." | mail -s "Export Warning" [email protected]
fi

Related Errors

• ORA-39083 - Object type failed to create (import side)
• ORA-39126 - Data Pump worker fatal error
• ORA-31655 - No data or metadata objects selected for job
• ORA-00942 - Table or view does not exist

Emergency Response

Quick Fix for Typo in Table Name

# Correct the table name (uppercase) and re-run:
expdp system/password \
  TABLES=MYSCHEMA.CORRECT_TABLE_NAME \  # Fix the typo
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=corrected_export.dmp

Quick Fix for Missing Schema

-- Verify the schema exists:
SELECT username FROM dba_users WHERE username = UPPER('&schema_name');

-- If it doesn't exist, check for variations:
SELECT username FROM dba_users WHERE username LIKE UPPER('%&partial_name%');

Post-Export Validation

# Verify the dump file contains the expected objects:
impdp system/password \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=export.dmp \
  SQLFILE=DATA_PUMP_DIR:validate_export.sql \
  SCHEMAS=MYSCHEMA

# Count CREATE TABLE statements in the generated SQL file:
grep -c "^CREATE TABLE" /path/to/DATA_PUMP_DIR_directory/validate_export.sql
# Compare against expected table count.