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
 ----------------------------------------- -------- ---------------------
 COLUMN1     VARCHAR2(30)
 COLUMN2     VARCHAR2(40)
 COLUMN3     VARCHAR2(20)

• 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

   2498333363867

• 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;

COLUMN1   COLUMN2     COLUMN3
------   -------      ------
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;

COLUMN1         COLUMN2 COLUMN3

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

line5        line5 line5

• First, get the SCN again, in order to have the second point in time to compare:

SQL> @scn

   2498333943172

• 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;

COLUMN1    COLUMN2    COLUMN3
--------  ----------  -----------
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.

Rafael.