Oracle 11g: Flashback Query, Flashback Query Version, Flashback Table

The flashback Technology uses undo data to retrieve information (except for flashback database). Ensure that you have the correct UNDO_RETENTION parameter definition to guarantee your flashback opération through a specific period of time.


FlashBack Query
Flashback query allows user to query the database and see what were the data as a specific point in the past.

To perform a query on the past you can write :

SQL> SELECT employee_id, salary from HR.employees AS OF TIMESTAMP <time_stamp in past>;

or using a specific SCN number :

SQL> SELECT employee_id, salary from HR.employees AS OF SCN #;

note: It is not possible to flashback query a table if a DDL request has been performed after your targeted SCN or TIMESTAMP.


FlashBack Query Version
Flashback query version allows the user to display all modifications performed on a set of data between two timestamps or to SCN.

SQL> select VERSIONS_XID, employee_id, salary from HR.employees VERSIONS BETWEEN TIMESTAMP <T1> AND <T2>;

or using SCN

SQL> select VERSIONS_XID, employee_id, salary from HR.employees VERSIONS BETWEEN SCN # AND #;

VERSIONS_XID is a pseudo-column that defines the transaction identifier responsible of the line modification (Deleted lines are also considered).

note: Only commited lines are taken into account.
note: Do not use the query version to query :

•  External tables
•  Temporary tables
•  Views
•  V$ views

note: It is not possible to perform a flashback query version if a DDL request has been performed between the two targeted timstamps or SCN.


FlashBack Table
Flashback table restore a table at a specific time or SCN.
You must :

1.  be granted FLASHBACK TABLE or FLASHBACK ANY TABLE
2.  have SELECT , UPDATE, DELETE and ALTER priviledges on the targeted table
3.  enable ROW MOVEMENT on the targeted tables

   SQL> ALTER TABLE <table_name> ENABLE ROW MOVEMENT;

To flashback a table at a specific time in the past :

SQL> FLASHBACK TABLE <table_name> TO TIMESTAMP to_timestamp( '01/12/2011 17:35:00', 'DD/MM/YYYY HH24:MI:SS');

note: The flashback table could not be performed on a tables in the SYSTEM tablespace, views, V$ views and external tables. It could not be also performed if a DDL request has be performed after the targeted point in time.
note: Flashback table generates redo and undo data.

It could be possible the flashback a table to a specific SCN or restore point.

SQL> FLASHBACK TABLE <table_name> TO SCN #;

SQL> FLASHBACK TABLE <table_name> TO RESTORE POINT <restore_point_name>;