PROBLEM:

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

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)

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.

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

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.