[SOLVED] SELECT AS OF a version before column drop

SELECT AS OF a version before column drop

1/ Does FLASHBACK and SELECT AS OF/ VERSION BETWEEN use the same source of history to fall back to ? This question is related to the second question.

2/ I am aware that FLASHBACK cannot go back before a DDL change.

My question is for SELECT AS OF, would it be able to select something before a DDL change. Take for example

CREATE TABLE T
(col1 NUMBER, col2 NUMBER)
INSERT INTO T(col1, col2) VALUES('1', '1')
INSERT INTO T(col1, col2) VALUES('2', '2')
COMMIT;
SLEEP(15)
ALTER TABLE T DROP COLUMN col2;
SELECT * FROM T
AS OF SYSTIMESTAMP - INTERVAL '10' SECOND;

Would the select return 2 columns or 1 ?

Pardon me I do not have a database at hand to test.

Solution

Any DDL that alter the structure of a table invalidates any existing undo data for the table. So you will get the error 'ORA-01466' unable to read data - table definition has changed.

Here is a simple test

CREATE TABLE T
(col1 NUMBER, col2 NUMBER);
INSERT INTO T(col1, col2) VALUES('1', '1');
INSERT INTO T(col1, col2) VALUES('2', '2');
COMMIT;
SLEEP(15)
ALTER TABLE T DROP COLUMN col2;


SELECT * FROM T
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' SECOND);

ERRROR ORA-01466 upon executing the above select statement.

However DDL operations that alter the storage attributes of a table do no invalidate undo data so that you can still use flashback query.