Oracle Flashback Query

 

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.

We can query the past data by specifying SCN with the following sample query.

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.

We can also specify the condition in queries.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *