Flashback started out as a feature in Oracle Database 9i. Although to be honest it is just the opening up of a mechanism that has been at the core of the Oracle Database from very early on: the ability to have concurrent sessions and transactions and allow transaction rollback and long running queries unaffected by transactions completed after the start of the query are all based on the same mechanism as flashback.
Flashback was great for demos and it was great for administrators. Developers however could not to very much with it. At least not until Oracle Database 11g when the Flashback Data Archive was introduced that provided fine grained control over which tables should have flashback data associated with it and which would not. At the time, the FDA was part of the Advanced Compression Database Option on top of the Enterprise Edition. So:great feature but for the happy few. In addition, there still were two main limitations with Flashback: history starts only at the day when the FDA is created. Nothing from before that day would be available. It’s a bit like the butterfly that does not have any of the memories of the caterpillar. The second limitation: Flashback did not record the context of the transaction in which the changes were applied. The identity of the perpetrator nor any other session characteristic were captured – so a straight out replacement for journaling tables capturing the audit trail was not offered by Flashback (it led me to an elaborate work around: https://technology.amis.nl/2011/02/06/database-transaction-recorder-adding-who-to-when-and-what-to-make-flashback-take-over-from-journalling-tables/).
Now for the good news on Flashback in Oracle Database 12c – good news that comes in three parts:
- As of 12c – Flashback will capture the session context of transactions. To set the user context level (determining how much user context is to be saved), use the
DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVELprocedure. To access the context information, use theDBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXTfunction. (TheDBMS_FLASHBACK_ARCHIVEpackage is described in Oracle Database PL/SQL Packages and Types Reference.) - As of 12c – you can construct and manipulate the contents of the Flashback Data Archive. In other words: you can create your own history. Which means that a flashback query can travel back in time to way beyond the moment you turned on the FDA. In fact, it can go to before the introduction of the Flashback feature in the Oracle Database and even before the launch of the Oracle RDBMS product. It is in your hands! Import and export of history using
DBMS_FLASHBACK_ARCHIVEprocedures to create a temporary history table, and then later importing that table into the designated history table after loading that table with the desired history data. The temporary history table can be loaded using a variety of methods, including Data Pump. Support is also included for importing user-generated history. If you have been maintaining history using some other mechanism, such as triggers, you can import that history into Flashback Data Archive. - As of 12c, Flashback Data Archive is available in every edition of the database (XE, SE, SE One, EE). See the Feature by Edition guide:
Note: Flashback Query and Flashback Versions query are also available in every edition of the database!
Resources
Oracle Documentation: http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_flashback.htm#ADFNS01011
Feature by Edition for Oracle Database: http://docs.oracle.com/cd/E16655_01/license.121/e17614/editions.htm#CJACGHEB
