ORA-31693: Table data object failed to load/unload and is being skipped due

Problem:

You are getting ORA-31693 and ORA-02354 when running expdp

Error message:

ORA-31693: Table data object "HR"."EMP" failed to load/unload and is being skipped due to error: ORA-02354: error in exporting/importing data ORA-01426: numeric overflow

Solution:

There can be different solutions depending on your environment.

1. Possible invalid or missing Datapump related objects in the database:

-- Connect to the database as user SYS, then execute catproc.sql SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql

2. If you have Oracle Data Redaction enabled, then you might need to drop it before export and re-create  after the import:

-- Before export -- Drop policy before DataPump export BEGIN DBMS_REDACT.DROP_POLICY (   object_schema        => 'OWNER',   object_name          => 'Table_Name',   policy_name          => 'REDACT_Policy_Name'); END; / -- After Import -- Add policy after DataPump import BEGIN DBMS_REDACT.ADD_POLICY(   object_schema    => 'HR',   object_name      => 'EMP',   column_name      => 'BIRTHDATE',   policy_name      => 'REDACT_EMP',   function_type    =>  DBMS_REDACT.RANDOM,   expression       => '1=1'); END; /

3. Install the fix for Bug 9110642, if available for your platform and Oracle version.

4. Do not use FLASHBACK_TIME or FLASHBACK_SCN options:

--Example: expdp scott/tigger DIRECTORY=datapump DUMPFILE=EXP_file.dmp FLASHBACK_TIME="TO_TIMESTAMP(SYSDATE)"  ...    Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA    ORA-31693: Table data object "TC"."RE$ACTION_IMP_TAB" failed to    load/unload and is being skipped due to error:    ORA-01466: unable to read data - table definition has changed

I hope this helps.

Please feel free to leave your questions or suggest improvements to this section.