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.

Oracle Flashback Architecture

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.)

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

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