Flashback Query ( As of Timestamp ) in Oracle Database

Hi,

I will explain what is the Flashback Query feature in Oracle in this article.

Read previous article before this.

Oracle Flashback Architecture

You can query table data that it existed at an past time via Oracle Flashback Query feauture. This query should be a past time through a time stamp or System Change Number (SCN).

Let’s go to make an example about Flashback Query to learn this feature very well. I will create sample table and insert Test data , then I will query past time of table via Flashback Query.

Create Test Table as follows

SQL> Create table MEHMET.SALIH(id number,name Varchar2(20));

Query the Current SCN of Database like following.

SQL> Select current_scn from v$database;

409505

Insert test data and commit it.

SQL> Insert into MEHMET.SALIH values(1,'Deveci');

Commit;

Query table.

SQL> Select count(*) from MEHMET.SALIH;

1

SQL> Select * from MEHMET.SALIH;

1   Deveci

But when we query table via Flashback Query like following, we have viewed past time of table.

SQL> Select count(*) from MEHMET.SALIH AS OF SCN 409505;

0

SQL> Select * from MEHMET.SALIH AS OF SCN 409505; 

1   Deveci

You can use AS OF TIMESTAMP instead of AS OF SCN like following.

SQL> Select * from MEHMET.SALIH AS OF TIMESTAMP SYSDATE - 1/24; 

1   Deveci

You cannot query for each scn in this way. The limiting factor here is related to undo retention and undo tablespace parameters.

You can learn related retention parameter with the following command.

SQL> show parameter undo_retention;

The value is in seconds. So you can view maximum Up to this value. If you increase this value, you can view more than past time 😉

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/