{"id":4260,"date":"2025-06-16T10:45:00","date_gmt":"2025-06-16T10:45:00","guid":{"rendered":"https:\/\/w3buddy.com\/?p=4260"},"modified":"2026-01-15T12:44:13","modified_gmt":"2026-01-15T07:14:13","slug":"how-to-fix-ora-31696-when-using-direct_path-import-in-oracle-data-pump","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/how-to-fix-ora-31696-when-using-direct_path-import-in-oracle-data-pump\/","title":{"rendered":"How to Fix ORA-31696 When Using DIRECT_PATH Import in Oracle Data Pump"},"content":{"rendered":"\n

When performing a schema or table import using Oracle Data Pump with the DIRECT_PATH<\/code> method, you might encounter the following error:<\/p>\n\n\n\n

ORA-31696: unable to export\/import TABLE_DATA:\"HRAPP\".\"EMPLOYEES\" using client specified DIRECT_PATH method\n<\/code><\/code><\/pre>\n\n\n\n
This error simply means Oracle couldn\u2019t use the DIRECT_PATH<\/code> method due to specific limitations on the table.<\/p>\n\n\n\n
Why It Happens<\/h2>\n\n\n\n
Oracle uses DIRECT_PATH<\/code> for faster data loads, but it cannot be used in the following situations:<\/p>\n\n\n\n
    \n
  • Table has enabled triggers<\/strong><\/li>\n\n\n\n
  • Table includes encrypted columns<\/strong><\/li>\n\n\n\n
  • Table has active constraints<\/strong> (foreign keys, check constraints)<\/li>\n\n\n\n
  • Table has unique\/function-based indexes<\/strong><\/li>\n\n\n\n
  • Table has LOBs or domain indexes<\/strong> with unsupported settings<\/li>\n<\/ul>\n\n\n\n
    \u2705 How to Fix ORA-31696 Step-by-Step<\/h2>\n\n\n\n
    Assume:<\/p>\n\n\n\n
      \n
    • Schema<\/strong>: HRAPP<\/code><\/li>\n\n\n\n
    • Table<\/strong>: EMPLOYEES<\/code><\/li>\n<\/ul>\n\n\n\n
      1\ufe0f\u20e3 Check and Disable Triggers<\/h3>\n\n\n\n
      -- View active triggers on the table\nSELECT trigger_name \nFROM dba_triggers \nWHERE owner = 'HRAPP' AND table_name = 'EMPLOYEES' AND status = 'ENABLED';\n\n-- Disable them one by one\nALTER TRIGGER HRAPP.TRG_EMP_INSERT DISABLE;\nALTER TRIGGER HRAPP.TRG_EMP_UPDATE DISABLE;\n\n-- Or disable all via loop\nBEGIN\n  FOR t IN (\n    SELECT trigger_name \n    FROM dba_triggers \n    WHERE owner = 'HRAPP' AND table_name = 'EMPLOYEES' AND status = 'ENABLED'\n  ) LOOP\n    EXECUTE IMMEDIATE 'ALTER TRIGGER HRAPP.' || t.trigger_name || ' DISABLE';\n  END LOOP;\nEND;\n\/<\/code><\/code><\/pre>\n\n\n\n
      2\ufe0f\u20e3 Disable Constraints<\/h3>\n\n\n\n
      -- Check constraints\nSELECT constraint_name, constraint_type \nFROM dba_constraints \nWHERE owner = 'HRAPP' AND table_name = 'EMPLOYEES';\n\n-- Disable check (C) and referential (R) constraints\nALTER TABLE HRAPP.EMPLOYEES DISABLE CONSTRAINT CK_EMP_STATUS;\nALTER TABLE HRAPP.EMPLOYEES DISABLE CONSTRAINT FK_EMP_DEPT;<\/code><\/code><\/pre>\n\n\n\n
      3\ufe0f\u20e3 Drop or Mark Indexes UNUSABLE<\/h3>\n\n\n\n
      -- View indexes\nSELECT index_name, uniqueness \nFROM dba_indexes \nWHERE owner = 'HRAPP' AND table_name = 'EMPLOYEES';\n\n-- Mark index unusable or drop (based on policy)\nALTER INDEX HRAPP.IDX_EMP_EMAIL UNUSABLE;\n-- DROP INDEX HRAPP.IDX_EMP_EMAIL;<\/code><\/code><\/pre>\n\n\n\n
      4\ufe0f\u20e3 Run Import with DIRECT_PATH<\/h3>\n\n\n\n
      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 &\n<\/code><\/code><\/pre>\n\n\n\n
      5\ufe0f\u20e3 Re-Enable Triggers, Constraints, and Indexes<\/h3>\n\n\n\n
      -- Enable triggers\nALTER TRIGGER HRAPP.TRG_EMP_INSERT ENABLE;\nALTER TRIGGER HRAPP.TRG_EMP_UPDATE ENABLE;\n\n-- Enable constraints\nALTER TABLE HRAPP.EMPLOYEES ENABLE CONSTRAINT CK_EMP_STATUS;\nALTER TABLE HRAPP.EMPLOYEES ENABLE CONSTRAINT FK_EMP_DEPT;\n\n-- Rebuild index if it was made unusable\nALTER INDEX HRAPP.IDX_EMP_EMAIL REBUILD;<\/code><\/code><\/pre>\n\n\n\n
      \ud83d\udccc Important Notes<\/h2>\n\n\n\n
        \n
      • If the table has encrypted columns<\/strong>, DIRECT_PATH<\/code> will not work<\/strong> at all\u2014use ACCESS_METHOD=AUTOMATIC<\/code> or remove the access_method<\/code> parameter.<\/li>\n\n\n\n
      • If performance is your goal, DIRECT_PATH<\/code> is faster, but only if all blockers are removed.<\/li>\n<\/ul>\n\n\n\n
        \ud83e\udde9 Summary<\/h2>\n\n\n\n
        The ORA-31696<\/code> 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<\/code>. Don\u2019t forget to revert everything after the import is complete.<\/p>\n","protected":false},"excerpt":{"rendered":"
        When performing a schema or table import using Oracle Data Pump with the DIRECT_PATH method, you might encounter the following error: This error simply means Oracle couldn\u2019t 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-4260","post","type-post","status-publish","format-standard","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4260","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=4260"}],"version-history":[{"count":2,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4260\/revisions"}],"predecessor-version":[{"id":4262,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4260\/revisions\/4262"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4260"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4260"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=4260"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}