How to Fix ORA-31696 When Using DIRECT_PATH Import in Oracle Data Pump

When performing a schema or table import using Oracle Data Pump with the DIRECT_PATH method, you might encounter the following error:

ORA-31696: unable to export/import TABLE_DATA:"HRAPP"."EMPLOYEES" using client specified DIRECT_PATH method

This error simply means Oracle couldn’t use the DIRECT_PATH method due to specific limitations on the table.

Why It Happens

Oracle uses DIRECT_PATH for faster data loads, but it cannot be used in the following situations:

Table has enabled triggers
Table includes encrypted columns
Table has active constraints (foreign keys, check constraints)
Table has unique/function-based indexes
Table has LOBs or domain indexes with unsupported settings

✅ How to Fix ORA-31696 Step-by-Step

Assume:

Schema: HRAPP
Table: EMPLOYEES

1️⃣ Check and Disable Triggers

-- View active triggers on the table
SELECT trigger_name 
FROM dba_triggers 
WHERE owner = 'HRAPP' AND table_name = 'EMPLOYEES' AND status = 'ENABLED';

-- Disable them one by one
ALTER TRIGGER HRAPP.TRG_EMP_INSERT DISABLE;
ALTER TRIGGER HRAPP.TRG_EMP_UPDATE DISABLE;

-- Or disable all via loop
BEGIN
  FOR t IN (
    SELECT trigger_name 
    FROM dba_triggers 
    WHERE owner = 'HRAPP' AND table_name = 'EMPLOYEES' AND status = 'ENABLED'
  ) LOOP
    EXECUTE IMMEDIATE 'ALTER TRIGGER HRAPP.' || t.trigger_name || ' DISABLE';
  END LOOP;
END;
/

2️⃣ Disable Constraints

-- Check constraints
SELECT constraint_name, constraint_type 
FROM dba_constraints 
WHERE owner = 'HRAPP' AND table_name = 'EMPLOYEES';

-- Disable check (C) and referential (R) constraints
ALTER TABLE HRAPP.EMPLOYEES DISABLE CONSTRAINT CK_EMP_STATUS;
ALTER TABLE HRAPP.EMPLOYEES DISABLE CONSTRAINT FK_EMP_DEPT;

3️⃣ Drop or Mark Indexes UNUSABLE

-- View indexes
SELECT index_name, uniqueness 
FROM dba_indexes 
WHERE owner = 'HRAPP' AND table_name = 'EMPLOYEES';

-- Mark index unusable or drop (based on policy)
ALTER INDEX HRAPP.IDX_EMP_EMAIL UNUSABLE;
-- DROP INDEX HRAPP.IDX_EMP_EMAIL;

4️⃣ Run Import with DIRECT_PATH

nohup impdp '"/ as sysdba"' DIRECTORY=DATA_PUMP_DIR DUMPFILE=employees_%U.dmp LOGFILE=emp_directpath_imp.log TABLES=HRAPP.EMPLOYEES ACCESS_METHOD=DIRECT_PATH TABLE_EXISTS_ACTION=REPLACE PARALLEL=4 &

5️⃣ Re-Enable Triggers, Constraints, and Indexes

-- Enable triggers
ALTER TRIGGER HRAPP.TRG_EMP_INSERT ENABLE;
ALTER TRIGGER HRAPP.TRG_EMP_UPDATE ENABLE;

-- Enable constraints
ALTER TABLE HRAPP.EMPLOYEES ENABLE CONSTRAINT CK_EMP_STATUS;
ALTER TABLE HRAPP.EMPLOYEES ENABLE CONSTRAINT FK_EMP_DEPT;

-- Rebuild index if it was made unusable
ALTER INDEX HRAPP.IDX_EMP_EMAIL REBUILD;

📌 Important Notes

If the table has encrypted columns, DIRECT_PATH will not work at all—use ACCESS_METHOD=AUTOMATIC or remove the access_method parameter.
If performance is your goal, DIRECT_PATH is faster, but only if all blockers are removed.

🧩 Summary

The ORA-31696 error during import happens due to restrictions on the table structure. By disabling triggers, constraints, and indexes, you make the table eligible for DIRECT_PATH. Don’t forget to revert everything after the import is complete.