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

Problem description:
--------------------------------
Whenever you tried to flashback of a table immediately after creating table and inserting data into it, or you tried to flashback of the table to a time or to a SCN before creating it then flashback table or flashback query fails with error message,

SQL> select * from TEST_FLASHBACK_FEATURE as of scn 1371097;
select * from TEST_FLASHBACK_FEATURE as of scn 1371097
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

Cause of The Problem:
----------------------------
If you give Flashback SCN or time before creating the table then it is expected as because the table was not there. But the question may come why after creating table the error appear. This is because smon_scn_time is updated every 6 seconds and hence that is the minimum time that the flashback query time needs to be behind the timestamp of the first change to the table. So, if after creating table within 5 seconds we run flashback query then error may come.

Workaround Example and Solution:
----------------------------------

In order to create the problem I have made a script named test_flashback.sql. The script will create table TEST_FLASHBACK_FEATURE and after inserting row it will select flashback SCN. Later we will play with that SCN.

1)Create the Script.

SQL> !vi /export/home/oracle/test_flashback.sql
CREATE TABLE TEST_FLASHBACK_FEATURE(A NUMBER)
/
INSERT INTO TEST_FLASHBACK_FEATURE VALUES(1)
/
COMMIT
/
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL

2.Execute The Script.

SQL> @/export/home/oracle/test_flashback.sql
Table created.

1 row created.

Commit complete.

GET_SYSTEM_CHANGE_NUMBER
------------------------
1371097

3.After getting SCN run two query for example flashback table and flashback query I ran and it failed.

SQL> select * from TEST_FLASHBACK_FEATURE as of scn 1371097;
select * from TEST_FLASHBACK_FEATURE as of scn 1371097
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

SQL> FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371097;
FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371097
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

4.Now I want to do flashback to the SCN of 6 seconds later. For that I converted the SCN to timestamp and manually added 6 seconds in to it.

SQL> SELECT SCN_TO_TIMESTAMP(1371097) FROM DUAL;
SCN_TO_TIMESTAMP(1371097)
---------------------------------------------------------------------------
17-MAY-08 07.20.44.000000000 AM

SQL> SELECT TIMESTAMP_TO_SCN('17-MAY-08 07.20.50 AM') FROM DUAL; --add 6 seconds.
TIMESTAMP_TO_SCN('17-MAY-0807.20.50AM')
---------------------------------------
1371098

5.Now perform flashback with the 6 seconds later SCN.

SQL> SELECT * FROM TEST_FLASHBACK_FEATURE AS OF SCN 1371098;
A
----------
1

SQL> FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371098;
Flashback complete.