ORA-39083: Object Type Failed to Create - Fix Data Pump

ORA-39083: Object Type Failed to Create with Error

Error Overview

Error Text: ORA-39083: Object type OBJECT_TYPE:"SCHEMA"."OBJECT_NAME" failed to create with error:

ORA-39083 is raised by Data Pump (impdp) when a specific object fails to be created during an import. The error appears in the import log file and is always followed by the Oracle error that caused the creation failure. The import continues processing other objects — ORA-39083 is non-fatal to the overall job unless the object is critical for subsequent objects to be created.

Unlike ORA-39126 (a worker crash), ORA-39083 is a clean, reported failure of a specific DDL statement. The full DDL statement that failed is usually printed in the import log after the error, making it straightforward to diagnose and fix.

Common Causes

1. Object Already Exists in the Target Schema

• A table, index, or constraint with the same name already exists
• Import is using TABLE_EXISTS_ACTION=SKIP and the object was pre-created differently
• A previous partial import left a half-created object

2. Target Tablespace Does Not Exist

• The source had a tablespace (e.g., USERS_TS, IDX_TS) that was not created on the target
• No REMAP_TABLESPACE parameter used to redirect to an existing tablespace
• Tablespace exists but the importing user has no UNLIMITED TABLESPACE or quota on it

3. Missing Privileges

• The import user does not have the privileges to create the specific object type
• Creating a view that references objects in another schema requires additional grants
• CREATE ANY DIRECTORY, CREATE DATABASE LINK, or other system privileges missing

4. Dependent Object Does Not Exist

• A view or procedure references a table that has not been imported yet
• A foreign key constraint references a parent table that failed to import
• A trigger references a package that was not exported or imported

5. Incompatible Data Types or Oracle Version Differences

• Source used a data type or feature not available in the target Oracle version
• A 23ai-specific data type (e.g., VECTOR) being imported into an older version
• Character set incompatibility in column definitions

Diagnostic Queries

Extract Failing Objects From the Import Log

# In the import log file, search for ORA-39083:
grep -A 5 "ORA-39083" /path/to/import.log

# List all object types that failed:
grep "ORA-39083" /path/to/import.log | awk -F: '{print $2}' | sort | uniq -c | sort -rn

Check for Pre-Existing Objects in the Target Schema

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

-- All existing objects in the target schema:
SELECT
  object_type,
  COUNT(*) AS object_count
FROM dba_objects
WHERE owner = UPPER('&target_schema')
GROUP BY object_type
ORDER BY object_type;

Verify Tablespace Existence and User Quota

-- Does the target tablespace exist?
SELECT tablespace_name, status, contents
FROM dba_tablespaces
WHERE tablespace_name = UPPER('&tablespace_name');

-- Does the import user have quota on the tablespace?
SELECT
  username,
  tablespace_name,
  bytes / 1024 / 1024  AS used_mb,
  max_bytes / 1024 / 1024 AS quota_mb
FROM dba_ts_quotas
WHERE username = UPPER('&import_user');

-- Does the user have UNLIMITED TABLESPACE?
SELECT grantee, privilege
FROM dba_sys_privs
WHERE grantee = UPPER('&import_user')
  AND privilege = 'UNLIMITED TABLESPACE';

Check Privileges of the Import User

-- System privileges granted to the import user:
SELECT privilege, admin_option
FROM dba_sys_privs
WHERE grantee = UPPER('&import_user')
ORDER BY privilege;

-- Object privileges that may be needed:
SELECT
  grantee,
  table_schema,
  table_name,
  privilege,
  grantable
FROM dba_tab_privs
WHERE grantee = UPPER('&import_user')
ORDER BY table_schema, table_name;

-- Roles granted to the import user:
SELECT granted_role, admin_option, default_role
FROM dba_role_privs
WHERE grantee = UPPER('&import_user')
ORDER BY granted_role;

Find Dependent Objects That May Be Missing

-- Objects that the failing object depends on:
SELECT
  name,
  type,
  referenced_owner,
  referenced_name,
  referenced_type
FROM dba_dependencies
WHERE owner = UPPER('&target_schema')
  AND name = UPPER('&object_name')
ORDER BY referenced_type, referenced_name;

-- Are those dependencies present in the target schema?
SELECT
  d.referenced_owner,
  d.referenced_name,
  d.referenced_type,
  o.status
FROM dba_dependencies d
LEFT JOIN dba_objects o
  ON d.referenced_owner = o.owner
  AND d.referenced_name = o.object_name
  AND d.referenced_type = o.object_type
WHERE d.owner = UPPER('&target_schema')
  AND d.name = UPPER('&object_name')
  AND o.object_name IS NULL;  -- NULL means the dependency is missing

Step-by-Step Resolution

1. Read the Full Error in the Import Log

In the impdp log, ORA-39083 always appears with the DDL statement that failed:

ORA-39083: Object type TABLE:"MYSCHEMA"."ORDERS" failed to create with error:
ORA-00959: tablespace 'ORDERS_DATA' does not exist
Failing sql is:
 CREATE TABLE "MYSCHEMA"."ORDERS" ("ID" NUMBER, ...) TABLESPACE "ORDERS_DATA"

The failing SQL tells you exactly what went wrong and what to fix.

2. Fix Missing Tablespace — Use REMAP_TABLESPACE

# Re-run impdp with tablespace remapping:
impdp system/password \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=export.dmp \
  LOGFILE=import2.log \
  SCHEMAS=MYSCHEMA \
  REMAP_TABLESPACE=ORDERS_DATA:USERS \
  REMAP_TABLESPACE=IDX_TS:USERS \
  TABLE_EXISTS_ACTION=REPLACE

3. Fix Object Already Exists — Choose the Right TABLE_EXISTS_ACTION

# SKIP: Leave the existing object as-is, do not import data
impdp ... TABLE_EXISTS_ACTION=SKIP

# REPLACE: Drop and recreate the object, then import data
impdp ... TABLE_EXISTS_ACTION=REPLACE

# TRUNCATE: Keep structure, truncate existing data, import new data
impdp ... TABLE_EXISTS_ACTION=TRUNCATE

# APPEND: Keep structure and existing data, append imported data
impdp ... TABLE_EXISTS_ACTION=APPEND

4. Fix Missing Privileges for the Import User

-- Grant required system privileges for a full schema import:
GRANT CREATE SESSION TO import_user;
GRANT CREATE TABLE TO import_user;
GRANT CREATE VIEW TO import_user;
GRANT CREATE PROCEDURE TO import_user;
GRANT CREATE TRIGGER TO import_user;
GRANT CREATE SEQUENCE TO import_user;
GRANT CREATE DATABASE LINK TO import_user;
GRANT UNLIMITED TABLESPACE TO import_user;

-- For importing into another user's schema (common with impdp as non-SYS):
GRANT IMP_FULL_DATABASE TO import_user;
-- Or more specifically:
GRANT CREATE ANY TABLE TO import_user;
GRANT CREATE ANY INDEX TO import_user;

5. Handle Dependent Objects by Importing in the Right Order

# Import tables first:
impdp system/password \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=export.dmp \
  SCHEMAS=MYSCHEMA \
  INCLUDE=TABLE

# Then import views, procedures, triggers, etc.:
impdp system/password \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=export.dmp \
  SCHEMAS=MYSCHEMA \
  INCLUDE=VIEW,PROCEDURE,FUNCTION,PACKAGE,TRIGGER \
  TABLE_EXISTS_ACTION=SKIP

6. Fix Foreign Key Constraint Failures

-- If foreign key constraints fail to import (parent table missing or data not loaded yet):
-- Step 1: Import with constraints disabled:
-- impdp ... EXCLUDE=CONSTRAINT

-- Step 2: After all data is loaded, re-run impdp for constraints only:
-- impdp ... INCLUDE=CONSTRAINT

-- Or add constraints manually after import:
ALTER TABLE myschema.orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id)
  REFERENCES myschema.customers(id);

7. Retry the Failed Object Manually

If only a few objects failed, create them manually using the DDL from the import log:

-- Copy the "Failing sql is:" statement from the import log and run it,
-- but first fix the issue (e.g., replace the tablespace name):

CREATE TABLE "MYSCHEMA"."ORDERS" (
  "ID"          NUMBER,
  "CUSTOMER_ID" NUMBER,
  "ORDER_DATE"  DATE
) TABLESPACE USERS;  -- Changed from ORDERS_DATA to USERS

8. Import a Specific Failing Object After Fixing the Issue

# Import only the specific object that failed:
impdp system/password \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=export.dmp \
  TABLES=MYSCHEMA.ORDERS \
  TABLE_EXISTS_ACTION=REPLACE

Prevention Strategies

1. Run a SQLFILE Dry Run Before Importing

# Generate DDL script from the dump without actually importing:
impdp system/password \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=export.dmp \
  SQLFILE=DATA_PUMP_DIR:import_preview.sql \
  SCHEMAS=MYSCHEMA

# Review import_preview.sql for tablespace names, constraints, and object types
# before running the actual import.

2. Prepare the Target Environment Before Importing

-- Create all required tablespaces:
CREATE TABLESPACE orders_data DATAFILE '/u01/oradata/orders_data01.dbf' SIZE 10G AUTOEXTEND ON;
CREATE TABLESPACE idx_ts DATAFILE '/u01/oradata/idx_ts01.dbf' SIZE 5G AUTOEXTEND ON;

-- Create the target schema with required privileges:
CREATE USER myschema IDENTIFIED BY password DEFAULT TABLESPACE orders_data;
GRANT IMP_FULL_DATABASE TO myschema;
GRANT UNLIMITED TABLESPACE TO myschema;

3. Always Use REMAP_TABLESPACE in Cross-Environment Imports

# Standard practice: always remap tablespaces when importing to a different environment:
impdp system/password \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=prod_export.dmp \
  REMAP_SCHEMA=PROD_SCHEMA:DEV_SCHEMA \
  REMAP_TABLESPACE=PROD_DATA:DEV_DATA \
  REMAP_TABLESPACE=PROD_IDX:DEV_IDX \
  SCHEMAS=PROD_SCHEMA

4. Monitor and Reconcile After Import

-- Compare object counts between source and target:
SELECT object_type, COUNT(*) AS count
FROM dba_objects
WHERE owner = 'MYSCHEMA'
GROUP BY object_type
ORDER BY object_type;

-- Find invalid objects after import:
SELECT object_name, object_type, status
FROM dba_objects
WHERE owner = 'MYSCHEMA' AND status = 'INVALID';

-- Recompile invalid objects:
EXEC DBMS_UTILITY.COMPILE_SCHEMA('MYSCHEMA', FALSE);

Related Errors

• ORA-39126 - Data Pump worker fatal error (more severe)
• ORA-39166 - Object not found for export
• ORA-00959 - Tablespace does not exist (common companion)
• ORA-01031 - Insufficient privileges (common companion)

Emergency Response

Quick Fix for Tablespace Error

# Most common fix — remap the missing tablespace:
impdp system/password \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=export.dmp \
  SCHEMAS=MYSCHEMA \
  REMAP_TABLESPACE=MISSING_TS:USERS \
  TABLE_EXISTS_ACTION=REPLACE

Quick Fix for Pre-Existing Object

# Replace existing objects:
impdp system/password \
  DIRECTORY=DATA_PUMP_DIR \
  DUMPFILE=export.dmp \
  SCHEMAS=MYSCHEMA \
  TABLE_EXISTS_ACTION=REPLACE

Post-Import Validation

-- Check for invalid objects after import:
SELECT object_name, object_type FROM dba_objects
WHERE owner = 'MYSCHEMA' AND status = 'INVALID';

-- Recompile:
EXEC DBMS_UTILITY.COMPILE_SCHEMA('MYSCHEMA', FALSE);

-- Confirm row counts match source (if source counts are known):
SELECT table_name, num_rows FROM dba_tables WHERE owner = 'MYSCHEMA' ORDER BY table_name;