Flashback- Part 3 (Flashback Versions Query)

Hi Everyone,

Here we are to continue our Flashback Saga! If you lost our first 2 posts about that and are in the mood for a good reading, please go through the links below:

Flashback – Part 1 (Flashback Drop)
Flashback – Part 2 (Flashback Query)
Flashback – Part 4 (Flashback Transaction Query)
Flashback – Part 5 (Flashback Table)
Flashback – Part 6 (Flashback Database)
Flashback – Part 7 (Flashback Data Archive)


Today we are going to discuss Flashback Versions Query, which has a strong link with the previous post, the Flashback Query (AS OF). With this feature, we are able to verify all changes made  between 2 points in time in the past, using SCN or a Timestamp. Of course, the Flashback Versions Query will retrieve only the committed data. Just like Flashback Query, the Flashback Versions Query is undo-based, so make sure your undo Tablespace and undo retention period is good enough for you.

What is the difference between Flashback Query and Flashback Versions Query? Well, basically using Flashback Query, you’ll see an EXACT point in the past for one single value. Using the Versions Query, you can see all versions of that value between two times in the past. Interesting huh?

This feature is enabled by using the clause VERSIONS BETWEEN in a SELECT statement, so then, you can view all the variations of some value between 2 points in the past.

Let’s do an example that may clarify our doubts:

We have our table already created on the previous Flashback Posts, so let’s use it:

SQL> desc grepora
 Name   Null?    Type
 ----------------------------------------- -------- ---------------------
  • Insert values into the table, and then get the SCN:
SQL> insert into grepora values ('line1', 'line1', 'line1');

1 row created.

SQL> commit;

Commit complete.
SQL> @scn

  • This is the script used to get the SCN:
set echo off feedback off lines 200 pages 0
column scn format 999999999999999
SELECT dbms_flashback.get_system_change_number scn FROM DUAL;
  • So, currently our table has only one value, which is:
SQL> select * from grepora;

------   -------      ------
line1      line1      line1

Let’s modify these values several times:

SQL> update grepora set column1='line2', column2='line2', column3='line2' where column1='line1';

1 row updated.

SQL> commit;

Commit complete.

SQL> update grepora set column1='line3', column2='line3', column3='line3' where column1='line2';

1 row updated.

SQL> commit;

Commit complete.

SQL> update grepora set column1='line4', column2='line4', column3='line4' where column1='line3';

1 row updated.

SQL> commit;

Commit complete.

SQL> update grepora set column1='line5', column2='line5', column3='line5' where column1='line4';

1 row updated.

SQL> commit;

Commit complete.
  • We still have only one line in our table, but we have changed it several times, with the above UPDATE commands.
SQL> select * from grepora;


------------------------------ ---------------------------------------- --------------------

line5        line5 line5
Now, it’s time. Let’s use this very nice feature to check all the versions of that this value has during two points in time.
  • First, get the SCN again, in order to have the second point in time to compare:
SQL> @scn

  • Now, we can compare all existent values for this table/columns having 2 SCN as reference (We could also use Timestamp for that).
SQL> select * from grepora versions between scn 2498333363867 and 2498333943172;

--------  ----------  -----------
line5       line5      line5
line4       line4      line4
line3       line3      line3
line2       line2      line2
line1       line1      line1

Done! With this example we could see all the versions of that table between 2 times in the past using SCN!

For the next post, we will check Flashback TRANSACTIONS query, which can go a little further than this one. We’ll see a little more next week!

Please let us know if you have any doubt or suggestion.

Have a wonderful week.



  1. Pingback: Flashback – Part 2 (Flashback Query) – GREPORA

  2. Pingback: Flashback – Part 7 (Flashback Data Archive) – |GREP ORA

  3. Pingback: Flashback – Part 6 (Flashback Database) – |GREP ORA

  4. Pingback: Flashback – Part 5 (Flashback Table) – |GREP ORA

  5. Pingback: Flashback- Part 4 (Flashback Transaction Query) – |GREP ORA

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.