PROBLEM:

While doing flashback a table to a prior timestamp, got below error.

11:20:45 SQL> FLASHBACK TABLE test2 TO TIMESTAMP TO_TIMESTAMP('2017-07-12 11:18:55', 'YYYY-MM-DD HH24:MI:SS');
FLASHBACK TABLE test2 TO TIMESTAMP TO_TIMESTAMP('2017-07-12 11:18:55', 'YYYY-MM-DD HH24:MI:SS')
                *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

CAUSE & SOLUTION:

  This error happens when a DDL change happened on that object and we are trying to flashback to a timestamp, before that DDL change.
i.e let’s say, DDL change happened to the database at 5 PM, and if you are trying to flashback to 4 PM,( before the DDL change), then flashback will fail with ORA-01466 error.
So flashback is possible only to a timestamp after the DDL change.

See the below DEMO:

1. Let’s truncate a table ( TRUNCATE is a DDL statement)

16:00:00 SQL> select count(*) from test2;

  COUNT(*)
----------
    133933


17:00:00 SQL> truncate table test2;

Table truncated.

Here I have truncated the table at 17:00 hrs. Now we will try to flashback to a timestamp before the DDL, say 16:30 hrs.

SQL>  FLASHBACK TABLE test2 TO TIMESTAMP TO_TIMESTAMP('2017-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS');
 FLASHBACK TABLE test2 TO TIMESTAMP TO_TIMESTAMP('2017-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS')
                 *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

Well, We hit a different error, because row movement was not enabled for the table. Let’s enable it.

SQL> alter table test2 enable row movement;

Table altered.


SQL>  FLASHBACK TABLE test2 TO TIMESTAMP TO_TIMESTAMP('2017-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS');
 FLASHBACK TABLE test2 TO TIMESTAMP TO_TIMESTAMP('2017-07-12 16:30:00', 'YYYY-MM-DD HH24:MI:SS')
                 *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

As expected we are getting the error ORA-01466. So flashback for a table is not possible if the timestamp is prior to the DDL change time.