What To Do When The Export Fails With ORA-01466?

Published: Thu, 08/08/19

The nightly backup job you have scheduled for your database, the datapump export job, starts failing with the following error:

ORA-31693: Table data object “HRAPP”.”REPORT_TABLE” failed to load/unload and is being skipped due to error:
ORA-02354: error in exporting/importing data
ORA-01466: unable to read data – table definition has changed

The first time this happens you don’t think much of this error, you just re-run the job during the day. As expected, the job completes successfully, and you file the incident as a one time only occurrence.

Then, a week passes by, and the export job fails again, same error message, but a different table. This export job failure seems to happen always on the same day, and it alternates between a few tables. Now you start getting curious to figure out what is causing the error and how to fix it.

There are two things to figure out, in order to find the solution. We need to know what the cause of the error is, and under what circumstances this error occurs.

Let’s tackle the first thing. When does this error occur?

There are two conditions for this error to occur:

1) the export datapump job is running using the FLASHBACK_SCN parameter in the parameter file. Using this parameter means that the export datapump operation is performed with data that is consistent up to the specified SCN.

2) the table specified in the first line of the error (in our case HRAPP.REPORT_TABLE), is changed during the export datapump job. Usually the table definition is changed by a TRUNCATE command, while the export is running.

Let’s answer the second question. Why does this error occur?

The error occurs because the LAST_DDL_TIME on the table is newer or more current than the FLAHBACK_SCN translated into a time. In other words, the LAST_DDL_TIME happened at a time after the FLASHBACK_SCN but before the export datapump job completed.

Here is a quick diagram to better understand what is going on

Now that we know when and why the error occurs, there are two options to fix this:

1) remove the FLASHBACK_SCN parameter from the expdp parameter file, and run the export without it. I do not recommend this method, as your export might not be consistent, and data could change during the export, making the dumpfile useless.

2) figure out what is causing the table definition change. Is there another job that interferes with the export job? Check what jobs you have scheduled in the database, in the crontab, or any other scheduler that you are using.
Verify the exact time the DDL operation occurred on the table(s):

select object_name,

to_char(last_ddl_time,'DD-MON-YY HH24:MI:SS') as time

from dba_objects where owner='HRAPP'

and object_name='REPORT_TABLE';

OBJECT_NAME TIME

------------------------- ----------------------

REPORT_ENTITY 01-AUG-19 20:25:00

In the case I was troubleshooting, the export job was running between 20:05 and 20:40. The above statement confirmed that the table was modified while the export was running, at 20:25.
If you identified the job that truncates the table, and are able to modify the run time for it, you can reschedule that job to run at a different time. If you do not have access to the job, or were not able to identify it, you can reschedule the export job. The latter, the export job might be easier to reschedule, just because as the DBA you have more control over it.

Remember - sharing is caring...

Forward this email to a friend!

You're the best!

Sincerely,

Diana Robete

diana@dbaparadise.com