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...
2 comments:
Its an interesting thought of WHY does a constraint act in this manner. The table and index blocks are unaltered.
My guess is that it is because the optimizer can use constraints in its query plan (eg This column is not null and indexed, so I know the same number of rows will be in the index as the table and so I'll scan the index not the table).
If you add or modify a constraint not previously in place, the optimizer has to be prevented from assuming that the old data conformed to the new constraint.
Fair enough that you cannot go back past DDL statements introducing or leaving out constraints... With a bit of imagination, you can think of the odd thing going wrong when using flashback!
Post a Comment