ORA-39826 Direct path load of view or synonym failed

I got ” ORA-39826 Direct path load of view or synonym failed  ”  error in Oracle database.

ORA-39826 Direct path load of view or synonym failed

Details of error are as follows.

ORA-39826: Direct path load of view or synonym could not be resolved.

Cause: A synonym or view could not be translated.

Action: Verify that the view or synonym is valid.

DataPump export log file contains the errors:

ORA-31693: Table data object "SCHEMA_NAME>"."<TABLE_NAME>" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-39826: Direct path load of view or synonym ("SCHEMA_NAME>"."<TABLE_NAME>") could not be resolved.

Direct path load of view or synonym could not be resolved

This ORA-39826 error is related to the synonym or view could not be translated.

Verify that the view or synonym is valid.

This is designed behavior. The fix for unpublished Bug 9847421 introduced a new set of checks for object name resolution which includes the generation of the ORA-39826 error message as outlined in Bug 14006984 – ORA-39826 RAISED ON 11.2.0.3, BUT ORA-942 IS EXPECTED closed with status ‘Not a Bug’.

The objects were dropped during the runtime of EXPDP.

Please check whether the objects reported in the error message exist or not. The example below is related to the object mentioned in above error message:

connect / as sysdba

select owner, object_name, object_type, last_ddl_time
from   dba_objects
where  owner = '<SCHEMA_NAME>' and object_name in ('<TABLE_NAME>');

If the query returns no rows, then you can expect the ORA-39826 is raised. You should make sure that the object exists when it is exported and it is not removed during the runtime of export. Dropping an object during the master control table of DataPump export is created and filled but before the actual table contents are exported, will result in these type of error.

Second case is as follows.

Below errors reported

ORA-31693: Table data object "USER"."CMP4$xxxxxx" failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-39826: Direct path load of view or synonym (USER.CMP4$xxxxxx) could not be resolved.

Tables with names like CMP3$xxxxxx or CMP4$xxxxxx (where xxxxxx is a number) creates during Compression Advisor and dropped after the Compression Advisor. If you see Tables with names like CMP3$xxxxxx or CMP4$xxxxxx (where xxxxxx is a number) left over after running Compression Advisor, it is likely because Compression Advisor failed at some point. These are interim tables created/used by Compression Advisor, which are normally dropped when it completes. You can safely drop those tables.

This is designed behavior. The fix for unpublished Bug 9847421 introduced a new set of checks for object name resolution which includes the generation of the ORA-39826 error message as outlined in Bug 14006984 – ORA-39826 RAISED ON 11.2.0.3, BUT ORA-942 IS EXPECTED closed with status ‘Not a Bug’.

The objects were dropped during the runtime of EXPDP.

Please check whether the objects reported in the error message exist or not. The example below is related to the object mentioned in above error message:

connect / as sysdba

select owner, object_name, object_type, last_ddl_time
from dba_objects
where owner = '<SCHEMA_NAME>' and object_name in ('<TABLE_NAME>');

If the query returns no rows, then you can expect the ORA-39826 is raised. You should make sure that the object exists when it is exported and it is not removed during the runtime of export. Dropping an object during the master control table of DataPump export is created and filled but before the actual table contents are exported, will result in these type of error.

If error reported for Tables with names like CMP3$xxxxxx or CMP4$xxxxxx (where xxxxxx is a number), then please restart the export again.

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

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