{"id":32404,"date":"2020-11-30T09:41:05","date_gmt":"2020-11-30T09:41:05","guid":{"rendered":"https:\/\/ittutorial.org\/?p=32404"},"modified":"2020-11-30T09:52:55","modified_gmt":"2020-11-30T09:52:55","slug":"ora-01466-unable-to-read-data-table-definition-has-changed","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/ora-01466-unable-to-read-data-table-definition-has-changed\/","title":{"rendered":"ORA-01466: unable to read data – table definition has changed"},"content":{"rendered":"

I got ” ORA-01466: unable to read data – table definition has changed<\/strong> ” error in Oracle database during impdp.<\/p>\n

<\/p>\n

 <\/p>\n

ORA-01466: unable to read data – table definition has changed<\/span><\/h2>\n

 <\/p>\n

Details of error are as follows.<\/p>\n

ORA-31693: Table data object \"MSDBA\".\"TEST_TABLE\" failed to load\/unload and is being skipped due to error:\r\nORA-02354: error in exporting\/importing data\r\nORA-01466: unable to read data - table definition has changed\r\nProcessing object type TABLE_EXPORT\/TABLE\/PROCACT_INSTANCE\r\nProcessing object type TABLE_EXPORT\/TABLE\/TABLE\r\n\r\nORA-01466:\u00a0Unable to read data -- Table definition has changed<\/strong>\r\n\r\nCause:\u00a0\u00a0<\/strong>This is a time-based read consistency error for a database object,\u00a0 such as a table or index. Either of the following may have happened: The query was parsed and executed with a snapshot older than the time the object was changed.\r\n\u00a0\r\nThe creation time-stamp of the object is greater than the current system time.\u00a0 This happens, for example, when the system time is set to a time earlier than the creation time of the object.\r\n\u00a0\r\nAction:\u00a0<\/strong>If the cause is an old snapshot, then commit or rollback the transaction and resume work. a creation time-stamp in the future, ensure the system time is set correctly. If the object creation time-stamp is still greater than the system time, then export the object's data, drop the object, recreate the object so it has a new creation time-stamp, import the object's data, and resume work.<\/pre>\n
 <\/p>\n\n
 <\/p>\n
The ORA-01466<\/strong><\/span> error is related with the table or index last DDL time<\/strong> is greater than the time of impdp<\/strong>. Namely someone may drop or truncate the table when you did this action.<\/p>\n
Or if you use flashback_scn option,\u00a0The value of SCN supplied to parameter flashback_scn does not correspond to the time of LAST_DDL_TIME from DBA_OBJECTS for the specific table.<\/p>\n
A table’s DDL may have changed due to a truncate job and LAST_DDL_TIME can end up ahead of the flashback_scn time supplied in the parameter.<\/p>\n
 <\/p>\n
 <\/p>\n
ORA-01466<\/span><\/h3>\n
In This example, a developer or business analyist created a job that ran a truncate command while datapump job was running.<\/p>\n
To solve this error, you should\u00a0Check if there is any DDL job which is causing LAST_DDL_TIME\u00a0<\/strong> <\/span>to be modified to newer value. If so, change the time of execution of that job.<\/p>\n
As a work around you can remove the FLASHBACK_SCN<\/span><\/strong><\/span>\u00a0<\/span> <\/strong>parameter until you get the solution for the DDL job.<\/p>\n
EXPDP syntax :\r\n\r\nexpdp userid=<LOGIN>\r\n\u00a0\u00a0\u00a0\u00a0 dumpfile=<DUMP_NAME>_%U.dmp\r\n\u00a0\u00a0\u00a0\u00a0 logfile=<LOG_NAME>.log\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\r\n\u00a0\u00a0\u00a0\u00a0 filesize=28000m\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\r\n\u00a0\u00a0\u00a0\u00a0 flashback_scn=$SCN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0<\/span><\/strong>\r\n \u00a0\u00a0\u00a0 parallel=8\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0  \r\n\u00a0\u00a0\u00a0\u00a0 cluster=N\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\r\n\u00a0\u00a0\u00a0\u00a0 full=y<\/pre>\n
This error can also be generated\u00a0 for FLASHBACK_TIME<\/strong><\/span>.<\/p>\n
To determine if you are\u00a0 facing this issue, execute following steps:<\/p>\n
    \n
  1. Check dba_objects for LAST_DDL_TIME :\n
    SQL> select object_name,last_ddl_time from dba_objects where owner='<SCHEMA_NAME>' and object_name='<TABLE_NAME>';<\/pre>\n
    \u00a0<\/span><\/li>\n
  2. Collect SCN timestamp\n
    SQL> select SCN_TO_TIMESTAMP(SCN_specified_with_export) from dual;<\/pre>\n
    \u00a0<\/span>
    \nIf the LAST_TIME is newer than the scn time, you are facing this issue.<\/li>\n
  3. Remove the job which is running any DDL command on the failing object.<\/li>\n<\/ol>\n
     <\/p>\n
     <\/p>\n
    Do you want to learn Oracle Datapump ( Export import ), then Click This Link and read the articles.<\/span><\/a><\/h4>\n
    Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-4<\/a><\/p><\/blockquote>\n