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 record
create table t (b varchar2(100));
insert into t values ('hello world - works');
commit;
-- Look at the data
select * from t;
-- What timestamp did I still see my data -19-NOV-08 02.09.23.478019000 PM +00:00
SELECT SYSTIMESTAMP FROM dual;
-- Create an index
create index t_idx on t (b);
-- Delete all the data from my table and commit
delete from t;
commit;
-- There's no data anymore, nice
select * from t;
-- And with the flashback technology I see my data
select * from t as of timestamp to_timestamp('19-NOV-08 02.09.23.478 PM');
-- Insert another record
insert into t values ('hello world - no go');
commit;
-- Do some DDL on the table now, add a constraint
alter 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 records
alter table t drop constraint t_pk;
delete from t;
commit;
-- Use flashback again
select * 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...
