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.
Pingback: Flashback – Part 2 (Flashback Query) – GREPORA
Pingback: Flashback – Part 7 (Flashback Data Archive) – |GREP ORA
Pingback: Flashback – Part 6 (Flashback Database) – |GREP ORA
Pingback: Flashback – Part 5 (Flashback Table) – |GREP ORA
Pingback: Flashback- Part 4 (Flashback Transaction Query) – |GREP ORA