In my last blog about Flashback Query “Flashback Query – Part 2” I had to admit that with Oracle Database Standard Edition it did not work as expected. So what if we are using Enterprise Edition?
This is a similar example with the same Flashback Data Archive and the same tables but with Oracle Enterprise Edition 12.1.0.2 with PSU160419 from April 2016.
As you can see the tables are added to the Flashback Archive and now I want to add the column changedate to the table “STATUS”.
… no eror message! Everything works.
Great: The application owner or users will not even realize that Flashback Data Archive has been activated regardless of the changes they make.
For the example the query shows that the new column has been added with the default value of the current date.
In Flashback Archive we can see that the corresponding flashback table has been added as both the Flashback tablespace and the Total Space columns contain valid values.
If we look into the schema browser we can see that the table SYS_FBA_HIST_93296 has been created as a partitioned table (that’s the same for Standard Edition) but it’s empty.
That’s what we probably expected. If you add a column to a table, there is no “old” value or before image to be stored.
But what happens, if we drop the column?
In this case the entire table content will be written into the Flashback Archive. So using Flashback Archive you should probably not decide to drop any column or make major changes to a table unless you drop the flashback archive table first.
Unfortunatelly I was not able to list the content of the table in Toad. I guess the link from the original table “STATUS” to the Flashback table “SYS_FBA_HIST_93296” is broken.
Conclusion
Flashback Data Archive is a great feature, which helps to simplify application development. Nevertheless, you need to be aware of the overhead it creates. But despite of the bug in Standard Edition I would give it a try. One of my customers had the following problem with his scheduler:
The scheduler is using Oracle database as the back end. Whenever a new job is created an insert will be made to the corresponding table. That’s what I expected. But changes to the job will end up in an update of the row and even more problematic: After the job successfully completed, the row will be deleted! So the customer has no idea, if a job has completed successfully or never run! As he has no access to the source we used Flashback Data Archive to write a log of the job activities. Now we not only have the information about the completion of the job but also about all the status in between.