Site icon IT Tutorial

Flashback Query ( As of Timestamp ) in Oracle Database


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;






Insert test data and commit it.


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




Query table.


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



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;



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

1   Deveci


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



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.

 2,965 views last month,  3 views today

Exit mobile version