In the first in this series of articles on the tools in the Oracle Flashback Technology armoury, I discussed what is available to application developers, the various means by which they can rewind the clock and correct recently-past data errors. However, if this was a world in which the worst thing that could happen was a small mix-up with data, what a happy world it would be. However, this is not Disneyland, and data errors are not the big, bad wolf at the door; it is errors that raze whole database objects that database administrators fear. But fortunately, Oracle Flashback can come to our rescue in those situations too.
First a quick recap: Oracle Flashback Technology is actually a clutch of different Oracle tools that enable developers and administrators reach into the immediate past of their database to recover from errors, without having to resort to point-in-time recovery options. For this reason, they are relatively fast and incur a relatively low logistical overhead (since the database does not need to suffer any downtime to implement them).
Flashback table enables you revert a table to its state at a specified timestamp or SCN. Any rows of data created after the parameterized timestamp disappear and any deleted rows zombie back to existence; in addition, associated attributes such as triggers, indexes and constraints remain unviolated. And all this magic occurs without even the slightest blip of interference affecting the rest of the database; it stays up and available.
Flashback table is able to sidestep the unpleasantness of restoring from backups by using information in the undo tablespace to restore the table.
Imagine this scenario: some catastrophe has befallen the EMP table and the developers are unable to unpick the data. All eyes turn to you: Save us, they beseech, wave your wand and erase the past hour. This is where you would find Flashback Table useful:
FLASHBACK TABLE EMP TO SYSTIMESTAMP – INTERVAL '60' MINUTE;
And it’s done. Day saved. Simple.
To use flashback table, however, there are a few prerequisites that need to be in place:
- You must have the FLASHBACK privilege on the table. Or you must have the FLASHBACK ANY TABLE system privilege.
- You must have the SELECT, INSERT, DELETE and ALTER privileges on the table.
- Row movement must be enabled on the table.
- Finally - and crucially - the information in the undo tablespace must extend back far enough to cover your timestamp or SCN.