Flashback Query Version in Oracle Database

Hi,

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

 

 

Read previous article before this.

 

Flashback version query let you versions of a specific row to be tracked during a specified time period. You need to use VERSIONS BETWEEN clause to enable Flashback version query feature like following.

 

 

Let’s go to make an example about Flashback version Query to learn this feature very well. I will create sample table and insert Test data , then I will track versions of a specific row via Flashback version Query.

 

 

 

Create table and insert data.

SQL> Create table MEHMET.SALIH(id NUMBER,name VARCHAR2(20));

Table created.

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

1 row created.

SQL> commit;

Commit complete.

Query Current SCN of Database.

 

SQL> Select current_scn from v$database;

CURRENT_SCN
-----------
12209868

SQL>

 

 

 

 

 

And perform some DML (Insert,Update,Delete) on this table.

SQL> insert into MEHMET.SALIH values(2,'SALIH');

1 row created.

SQL> commit;

Commit complete.



SQL> Update MEHMET.SALIH SET name='Mehmet' where id=1;

1 row updated.

SQL> commit;

Commit complete.



SQL> Delete MEHMET.SALIH where id=2;

1 row deleted.

SQL> commit;

Commit complete.



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

1 row created.

SQL> commit;

Commit complete.

 

Query Current SCN again.

 

SQL> Select current_scn from v$database;

CURRENT_SCN
-----------
12209893

 

Query table now.

SQL> select * from MEHMET.SALIH;

ID NAME
---------- --------------------
1 Mehmet
1 Deveci

 

 

 

 

Lets go to see changes of table like following.

 

SQL> select versions_startscn, versions_starttime,
versions_endscn, versions_endtime,
versions_xid, versions_operation from MEHMET.SALIH VERSIONS BETWEEN SCN 12209868 AND 12209893;



You can see all operations start time and end time and also operation type (Insert, Delete, Update etc.)

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 *