Oracle Flashback Query allows us to query as much as possible of the past committed data. We can query the data in the past over the time stamp or system change number (SCN).
For Flashback Query use, the flashback feature of the database does not need to be turned on.
Oracle flashback query usage areas:
- Used to restore deleted or incorrectly altered information.
- It is used to compare the current data with the previous version.
- It is used to control the state of the data in the process at a given time.
- Oracle Flashback Query brings historical data directly in the database without any restore-recovery.
- Allows an application to self-correct end-user errors.
The data you query with Flashback Query is undo data. So the past time you can access is related to the size of the undo tablespace and how high your undo retention parameter is.
For Flashback Query, AS OF SCN and AS OF TIMESTAMP usages are available.
AS OF SCN:
By specifying the System Change Number (SCN), we can query the history of the data.
You can learn the SCN information before and after a data change with the following query. To query with SCN, SCN information will be required before the change.
|
1 |
SQL> SELECT current_scn, SYSTIMESTAMP FROM v$database; |
We can query the past data by specifying SCN with the following sample query.
|
1 |
SQL> select * from user_name.table_name as of scn 342340432; |
AS OF TIMESTAMP :
By giving a specific date, we can query the history of the data. We can query the history of data by specifying the date with the example query below.
|
1 |
SQL> select * from user_name.table_name as of timestamp to_date('20.12.2016 12:00:00','dd.mm.yyyy hh24:mi:ss'); |
We can also specify the condition in queries.
|
1 |
SQL> select * from user_name.table_name as of timestamp to_date('20.12.2016 12:00:00','dd.mm.yyyy hh24:mi:ss') where column_name=value; |
