ORA-31693: Table data object failed to load/unload error

I got ” ORA-31693: Table data object failed to load/unload error ”  error in Oracle database.

ORA-31693: Table data object failed to load/unload error

Details of error are as follows.

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

Cause: Table failed to load or unload due to some error.
Action: Check load/unload error, correct problem and retry command.

Import using DataPump will fail with the following errors when the export dump does not contain information about the metadata:

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
ORA-31693: Table data object "TEST"."TBL" failed to load/unload and is being skipped due to error:
ORA-31603: object "TBL" of type TABLE not found in schema "TEST"
ORA-31693: Table data object "TEST"."DEPT" failed to load/unload and is being skipped due to error:
ORA-31603: object "DEPT" of type TABLE not found in schema "TEST"
ORA-31693: Table data object "TEST"."EMP" failed to load/unload and is being skipped due to error:
ORA-31603: object "EMP" of type TABLE not found in schema "TEST"
ORA-31693: Table data object "TEST"."TEST2" failed to load/unload and is being skipped due to error:
ORA-31603: object "TEST2" of type TABLE not found in schema "TEST"
. . imported "TEST"."BONUS" 0 KB 0 rows
. . imported "TEST"."TAB1" 0 KB 0 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 4 error(s) at 09:28:41

Table data object string failed to load/unload and is being skipped due to error: string

This ORA-31693 error is related with the Table failed to load or unload due to some error.

When you perform a DataPump export and specify CONTENT=DATA_ONLY, the dump file will not contain the metadata information of the schema objects.

During DataPump import into a different schema or in the same schema where the metadata does not exists, the errors mentioned above will occur.

Confirm, whether the export dump was taken with CONTENT=ALL option. You can verify that by using the SQLFILE parameter in the impdp command line. Then DataPump generates a text file which contains the metadata information that was loaded in the export dump files. If SQLFILE contains no metadata information, then the dump was not exported with CONTENT=ALL.

To solve this error, either pre-create the objects before the import or export using CONTENT=ALL parameter.

Other case is as follows.

1. When performing a full database consistent export with Export Data Pump from a 12.1.0.1 source database, the following errors occur:

$ expdp <LOGIN>/<PASSWORD> directory=<DIRECTORY_NAME> dumpfile=<DUMP_NAME>.dmp logfile=<LOG_NAME>.log reuse_dumpfiles=y full=y flashback_time=systimestamp

Export: Release 12.1.0.1.0 - Production on Tue May 27 10:25:38 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "<LOGIN_SCHEMA>"."SYS_EXPORT_FULL_01":  <LOGIN>/******** directory=<DIRECTORY_NAME> dumpfile=<DUMP_NAME>.dmp logfile=<LOG_NAME>.log reuse_dumpfiles=y full=y flashback_time=systimestamp
Estimate in progress using BLOCKS method...
Processing object type
DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
...
ORA-31693: Table data object "SYS"."KU$_USER_MAPPING_VIEW" failed to load/unload and is being skipped due to error:
ORA-01031: insufficient privileges
...

For this job, similar ORA-1031 (insufficient privileges) errors are reported for:

ORA-31693: Table data object "ORDDATA"."ORDDCM_DOCS" failed to load/unload and is being skipped due to error:
ORA-31693: Table data object "SYS"."FGA_LOG$FOR_EXPORT" failed to load/unload and is being skipped due to error:
ORA-31693: Table data object "SYSTEM"."SCHEDULER_JOB_ARGS" failed to load/unload and is being skipped due to error:
ORA-31693: Table data object "SYSTEM"."SCHEDULER_PROGRAM_ARGS" failed to load/unload and is being skipped due to error:
ORA-31693: Table data object "SYS"."AUDTAB$TBS$FOR_EXPORT" failed to load/unload and is being skipped due to error:
ORA-31693: Table data object "SYS"."DBA_SENSITIVE_DATA" failed to load/unload and is being skipped due to error:
ORA-31693: Table data object "SYS"."DBA_TSDP_POLICY_PROTECTION" failed to load/unload and is being skipped due to error:
ORA-31693: Table data object "SYS"."NACL$_ACE_EXP" failed to load/unload and is being skipped due to error:
ORA-31693: Table data object "SYS"."NACL$_HOST_EXP" failed to load/unload and is being skipped due to error:
ORA-31693: Table data object "SYS"."NACL$_WALLET_EXP" failed to load/unload and is being skipped due to error:
ORA-31693: Table data object "WMSYS"."WM$EXP_MAP" failed to load/unload and is being skipped due to error:

2. The problem reproduces when the full database export DataPump job is done with FLASHBACK_TIME as well as with FLASHBACK_SCN.

3. The problem also reproduces if the following privileges are granted to the schema that runs the Export Data Pump job:

-- following privileges do not solve the problem

connect / as sysdba
alter user <USERNAME> quota unlimited on system;
grant unlimited tablespace to <USERNAME>;
grant flashback any table to <USERNAME>;

1. As a workaround, grant the FLASHBACK ANY TABLE privilege and FLASHBACK privilege on the SYS-owned tables that were reported in the earlier export job to the EXP_FULL_DATABASE role, and re-run the export job, e.g.:

connect / as sysdba
grant flashback any table to exp_full_database;

grant flashback on SYS.KU$_USER_MAPPING_VIEW to exp_full_database;
grant flashback on SYS.FGA_LOG$FOR_EXPORT to exp_full_database;
grant flashback on SYS.AUDTAB$TBS$FOR_EXPORT to exp_full_database;
grant flashback on SYS.DBA_SENSITIVE_DATA to exp_full_database;
grant flashback on SYS.DBA_TSDP_POLICY_PROTECTION to exp_full_database;
grant flashback on SYS.NACL$_ACE_EXP to exp_full_database;
grant flashback on SYS.NACL$_HOST_EXP to exp_full_database;
grant flashback on SYS.NACL$_WALLET_EXP to exp_full_database;

$ expdp <LOGIN>/<PASSWORD> directory=<DIRECTORY_NAME> dumpfile=<DUMP_NAME>.dmp logfile=<LOG_NAME>.log reuse_dumpfiles=y full=y flashback_time=systimestamp
...
. . exported "SYS"."KU$_USER_MAPPING_VIEW"          6.078 KB      39 rows
. . exported "ORDDATA"."ORDDCM_DOCS"                252.9 KB       9 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT"             16.62 KB       0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS"          8.351 KB       0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS"      9.476 KB      12 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT"          5.929 KB       2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA"             8.039 KB       0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION"     7.625 KB       0 rows
. . exported "SYS"."NACL$_ACE_EXP"                  9.906 KB       1 rows
. . exported "SYS"."NACL$_HOST_EXP"                 6.890 KB       1 rows
. . exported "SYS"."NACL$_WALLET_EXP"               5.898 KB       0 rows
. . exported "WMSYS"."WM$EXP_MAP"                   7.695 KB       3 rows
...

– OR –

2. Apply Patch 19238926 if available for your platform and version (or request this fix from Oracle Support), and re-run the Export Data Pump job.

– OR –

3. Upgrade to 12.2 or later where the fix is included

Do you want to learn Oracle Database for Beginners, then read the following articles.