30 June 2008

FLASHBACK TABLE vs. DBA_OBJECTS.LAST_DDL_TIME

NOTE: This post originally appeared on The Pythian Group blog on 6 June 2008, and is reposted here with permission.

A little over a week ago, a teammate and I were trying to use Oracle’s FLASHBACK TABLE to undo an “oops” UPDATE statement that a client’s developers had run on one of their test databases, clearing data from two columns in all rows of the table. The statement was actually part of a script that also contained ALTER TABLE statements to add columns. This is important to note because FLASHBACK TABLE will only let you go back as far as the most recent DDL against that table. To quote the SQL reference, “Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.”