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>;

This problem is caused by:
Bug 18844843 – ORA-31693 ORA-01031 ON SOME OBJECTS IN 12CR1 WHEN EXPDP FULL AND FLASHBACK_TIME, which is superseded by:

unpublished Bug 19238926 – ORA-1031 instead of ORA-942 for users with SELECT_CATALOG_ROLE if fix 18844843 present, fixed in 12.2.
Check Note 19238926.8 – ORA-1031 instead of ORA-942 for users with SELECT_CATALOG_ROLE if fix 18844843 present

 

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.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 644 views last month,  1 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply