In my last blog “Flashback Functions – One Name, different Techniques“, I wrote about Flashback Functions. In this blog I will again write about Flashback Query.
Flashback query is actually the ability to read the past content of a table. Oracle Database uses two techniques to accomplish this:
- Rollback/Undo Segments
- Flashback Data Archive
Rollback/Undo Segments
Since Oracle 9i it’s been possible to add the „AS OF“ clause to a SELECT statement. In theory you can use an SCN but it’s more realistic to add a timestamp.
Example:
To begin, I used a table called „PERSONEN“ (which is similar to „CUSTOMERS“). To show the flashback query I changed the surname (NACHNAME) of the row with the ID 100389.
From now on every query will – as we all know and expect – read the new surname „Ahrends“. But with flashback query we are also able to select the “old” value.
To analyze the statement I used Toad for Oracle’s brillant tracing capabilities:
Now I’m executing the two statements:
- The current surname
- The “old” surname
Looking into the trace we see that the first statement is very simple while the second statement is executing a subquery because it needs to find the corresponding SCN for the timestamp we were using.
But the time window is very small to capture these changes. So if the data is no longer available in the UNDO segments you end up with an “ORA-01555 snapshot too old”. So no chance to read data, which is older than a few hours.
Flashback Data Archive
This is where the second technology comes into play: Flashback Data Archive. This feature was introduced with Oracle 11g as the “Total Recall” option to the Enterprise Edition. But it’s also available for free in all Editions since 11.2.0.4 and, of course, with 12c. For the next example I was using Oracle Standard Edition Two 12.1.0.2 with the PSU 160419 (April 2016).
First I have to create a Flashback Archive. Again, I used Toad for Oracle to do this. I called it “DEMOFDA”. It’s by conincidence that the tablepsace is also called DEMOFBA. There is nothing special with that tablespace so you can use whichever you want (except SYSTEM, SYSAUX, UNDO and TEMP, of course).
Part of the definition of the Flashback Area is the retention period. In my case I set the retention to one year, so 365 days:
The Flashback Area is only an area, so it doesn’t occupy space or have any other implications to the database or schema – and it is not dedicated to one schema.
Flashback Archive will be enabled only for certain tables. In my example I will select the entire schema “DEMO”.
Hint: With “View/Edit Query” you are able to filter the tables in Toad.
Unfortunatelly, Flashback Archive does not support tables with data type LONG.
Hint: The schema which owns the tables must have sufficient quota on the Flashback Archive tablespace.
Now I have Flashback Archive enabled for the tables. Looking at the right hand grid in Toad the flashback tablespace name is empty and the total space of the flashback table is 0. This is because the flashback tables will be created on the fly with the first DML.
As can be seen below, the flashback tablespace and total space columns have valid data as soon as the first change occurs:
But what about the queries?
Let’s trace it again.
It’s a huge difference to the first example with the Undo Tablespace. Instead of one subquery we now have 9 (!) and the execution time is (even that this is in milliseconds) remarkable.
If you look at the second subquery a table named like “SYS_FBA” is shown. This is the corresponding flashback archive table. To see the link between the tables you can use the view „DBA_FLASHBACK_ARCHIVE_TABLES“.
And this is the dataset in the Flashback Archive table.
After this very successful example I had to do some additional tests to show what happens, if you add a column to a table. As an example I added the column “CHANGEDATE” to the table “STATUS”.
And this is the result:
Table or View does not exist! But the table STATUS exists as you can see in Toad. But unfortunately the corresponding Flashback Table does not exist yet.
This means that you are not able to change a table as long as the corresponding flashback table does not exist. While doing some more tests I ended up in a mess. Even adding some rows to the STATUS table did not cause the corresponding flashback table to show up. I dropped the flashback area and tables and recreated them without success.
So I guess I have to invest some more time to find out what went wrong. I must admit that from my point of view there is still “room for improvement”.
No comments on “Flashback Query – Part 2”