Oracle Flashback Query

Oracle Flashback Query allows the use of select query with AS OF. It allows us to query as much of the committed data in the past as possible. We can query the past data via time stamp or system change number (SCN).

To use Flashback Query, the database’s flashback feature does not need to be turned on.

Oracle Flashback Query usage areas:

• It is used to retrieve deleted or erroneously changed information.
• It is used to compare current data with its previous state.
• It is used to control the status of transactional data at a certain time.
• Oracle Flashback Query retrieves historical data directly from the database without any restore or recovery process.
• It allows an application to self-correct end-user errors.

Since the data you will query with Flashback Query will be undo data, it is directly related to the size of your undo table space and how high your unto retention parameter is.

AS OF SCN and AS OF TIMESTAMP can be used for Flashback Query.

AS OF SCN :

By specifying the System Change Number (SCN), we can query the past state of the data.

You can learn the SCN information before and after a data change with the following query. To query with SCN, you will need the SCN information before the change.

SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database;

We can query historical data by specifying SCN with the sample query below.

SQL> select * from user.table as of scn 342340432;

AS OF TIMESTAMP :

We can query the past state of the data by specifying a specific date. We can query past data by specifying a date with the sample query below.

SQL> select * from user.table as of timestamp to_date('20.12.2016 12:00:00','dd.mm.yyyy hh24:mi:ss');

We can also specify conditions in queries.

SQL> select * from user.table as of timestamp to_date('20.12.2016 12:00:00','dd.mm.yyyy hh24:mi:ss') where column=value;