11g A Brief History of FLASHBACK Functionalities Oracle 11g New Features Tips by Donald BurlesonJune 29, 2015

Oracle 11g New Features Tips

The first flashback functionalities came with the dbms_flashback packages within the release of Oracle 9.0.  Flashback functionalities enabled the DBA to specify a consistency point in time for the session. By using dbms_flashback.enable_at_time(my_timestamp), a session could be put into a state which read the consistent image of the database as it was at a point in time in the past.

This was enhanced in version 9.2 when Oracle introduced the FLASHBACK QUERY functionality. The flashback query enabled users to query data as it appeared in the past, using a normal SQL:

SELECT ?
  FROM?
    AS OF TIMESTAMP|SCN
   WHERE?
?;

Reading data by specifying a timestamp or a system change number (SCN) was also common at the time.  

This concept was extended in Oracle's first 10g release on a row level. Consequently, the FLASHBACK VERSIONS QUERY functionality could be used to view all versions of a given row between two timestamps and two SCNs.

Once the transactions had been identified, it was also possible to look at the undo_sql column of a view called flashback_transaction_query . The transaction's ID, using the pseudo column versions_xid could also be found. Here, changes which were made by the same transaction could be found, per the appropriate SELECT ANY TRANSACTION system privilege.

SELECT UNDO_SQL
  FROM flashback_transaction_query
    WHERE XID=HEXTORAW('my_transaction-id');

This gave the principal option to spool the undo sql from the flashback_transaction_query view and execute it to undo changes. However, this was not practicable because of dependencies in transactions and applications which made it difficult to undo things in the correct sequence. Luckily, this has been addressed in Oracle 11g of which there will be a closer look at the new functionality shortly.

All flashback functionalities discussed so far have utilized information from the undo segments in order to read the consistent image of data in the past. These types of functionalities do not change anything in the database, but instead read data as it was in the past. UNDO data is the logical information needed to undo a change. This being, if the logical information needed to undo a change is overwritten in between, an error message appears explaining that it is not possible to reconstruct the consistent data in the past. However, 10gR1 introduced a number of other extensively new functionalities under the name FLASHBACK.

An example of the unique 10gR1 functionalities is the FLASHBACK DROP. The flashback drop utilized the so-called recycle bin which could be disabled with the RECYCLEBIN parameter in Oracle 10gR2.  The RECYCLEBIN had been enabled by default in prior releases, and in 10gR1 this parameter was the hidden parameter  _recyclebin.. If a DBA dropped a table in 10g, it would be internally renamed and the segment would stay where it was. These extents were available for reuse or review, in dba_free_space, but the server tries not to use them as long as possible. Oracle starts reusing these extents before auto extending a datafile. Once the server has reused the extents it is not possible to flashback to before drop. This enabled the user to flashback the drop, and simply a renames the object back to its original name. Indexes and triggers are also flashed back.

M Foreign key constraints are not flashed back with the table. They must be            manually recreated!

Since the object remained the same as before the drop operation, it also became possible for the DBA to read from it using the new name.  The FLASHBACK QUERY also offered the ability to read the object as it was in the past by using the new name and SELECT.

Another new feature in Oracle Database 10gR1 was FLASHBACK TABLE. This gave the ability to put a table back into the state it was in a previous point by applying undo data. FLAHSBACK had previously only been possible in a tablespace with automatic segment space management (ASSM) and enabled ROW MOVEMENT on the table intended to use flashback. Until 10gR2, ASSM had been the default for tablespaces. ALTER TABLE my_table ENABLE ROW MOVEMENT allows the server to change the physical address of a row exceptionally. The ROWID is normally assigned with insertion, and valid for the life time of a row.

FLASHBACK TABLE my_table
TO TIMESTAMP|SCN;

The DBA was limited in the inability to flashback to before a DDL statements because if the definition of the table changed in between the flashback operation, it would error out.