ORA-01466: unable to read data - table definition has changed

Question:  I am executing dbms_flashback.enable_at_time and I get this error:


ORA-01466: lecture des donnÚes impossible - DÚfinition de tables modifiÚe


How do I fix the ORA-01466 error?


Answer:  The "table definition has changed" error happens when a SQL statement detects that the table or index last DDL time is greater than the time that the task (not always a SQL statement) was parsed for execution. 


The ORA-01466 error can happen when a SQL statement is parsed after the table or index has changed (via DDL) and the SQL is executing with an old snapshot of the object DDL.  The ORA-01466 error is also thrown when you change the system time to a date in the future while tasks are executing.


The documentation notes this on the ORA-01466 error:


ORA-01466: Unable to read data -- Table definition has changed


Cause:  This is a time-based read consistency error for a database object,  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.
 
The creation time-stamp of the object is greater than the current system time.  This happens, for example, when the system time is set to a time earlier than the creation time of the object.
 
Action: 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.


Here are specific areas for the ORA-01466 error:


Possible Bugs and the ORA-01466 error:  Rampant author and Oracle ACE Laurent Schneider notes what appears to be a bug causing a phantom ORA-01466 error:


I made a test on my notebook by running the script 1,000 times on various versions testing for the ORA-01466 error:


10.2.0.3 : reproduced 97.2%
9.2.0.8 : reproduced 96.9%
10.1.0.5 : reproduced 98.7%
11.1.0.5 beta : reproduced 94.8%


All versions affected, none consistently.


ORA-01466 on Data Pump Export:  You can overcome this error when running expdp by removing the "flashback_scn= <ID>" export parameter, being aware of the risks.


ORA-01466 error with Flashback:  The Oracle dbms_flashback Query is enabled and disabled using the dbms_flashback package. The point in time of the flashback can be specified using the SCN or the actual time, and you will get a ORA-01466 error when you set the enable_at_time to a date in the future:


exec dbms_Flashback.enable_at_time('28-AUG-44 11:00:00');


ORA-01466 error with SQL: The most root cause of the ORA-01466 error is a super-long-running transaction where somebody issued an alter table or alter index statement while the SQL was executing.  The most common solution is to commit (or rollback) the transaction, and re-execute the SQL statement.