Flashback Query in Oracle Database

Hi,

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

 

 

Read previous article before this.

 

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 😉

 

 

 

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply

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