Flashback table data prevented by a constraint

When I was reorganizing some tables I deleted some data which at the end I wanted to reuse.
I was pretty confident that with the flashback technology in the Oracle database I could get my data again. So I was surprised when I got the message “ORA-01466: unable to read data - table definition has changed”.

I thought I had done something wrong so I build a small test case to see what was happening.

-- Create table with one recordcreate table t (b varchar2(100));insert into t values ('hello world - works');commit;-- Look at the dataselect * from t;-- What timestamp did I still see my data -19-NOV-08 02.09.23.478019000 PM +00:00SELECT SYSTIMESTAMP FROM dual;-- Create an indexcreate index t_idx on t (b);-- Delete all the data from my table and commitdelete from t;commit;-- There's no data anymore, niceselect * from t;-- And with the flashback technology I see my dataselect * from t as of timestamp to_timestamp('19-NOV-08 02.09.23.478 PM');-- Insert another recordinsert into t values ('hello world - no go');commit;-- Do some DDL on the table now, add a constraintalter table t add constraint t_pk primary key (b)select * from t;-- I still see my data at this stage, but now we drop the constraint and delete the recordsalter table t drop constraint t_pk;delete from t;commit;-- Use flashback againselect * from t as of timestamp to_timestamp('19-NOV-08 02.09.23.478 PM');-- I can't get to my data anymore

When reading through the documentation this made it clear: “Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table”.

Oracle does have limits ;-) and time for me to read through the documentation again…