Welcome to the first offering from Impartica’s trainers’ blog. The mandate here is to introduce you to an advanced or product specific feature that we don’t get time to cover in the courses. In this first posting I’m going to look at a feature of SQL that’s specific to recent versions of Oracle RDBMS, and it comes under the heading of Flashback SQL.
Not only does this have a cool name, it’s actually a cool feature.
Flashback was originally introduced in Oracle 9i, and improved in 10g. The feature I’m going to demonstrate today – the SELECT .. AS OF TIMESTAMP clause – is available in Oracle 10g Release 2 and higher.
Flashback allows you to view a table as it was at a previous point in time. Easily. Really easily, actually.
Rather than explain that any further I’ll show you the SQL and let you see for yourself. If you’ve never seen this feature before, I predict you’ll be impressed!
First I’ll set up my test table and data:
CREATE TABLE staff (full_name VARCHAR(20));
INSERT INTO staff (full_name) VALUES ('Susan Smith');
INSERT INTO staff (full_name) VALUES ('Harry Tomlinson');
INSERT INTO staff (full_name) VALUES ('Stuart Haines');
COMMIT;
SELECT * FROM staff;
FULL_NAME
--------------------
Susan Smith
Harry Tomlinson
Stuart Haines
Okay, nothing new so far. But what we’re going to do now is to change the data, commit the changes, but use Flashback SQL to go back and see the data as it is now.
To do this we’re going to use a timestamp, so let’s just capture the current timestamp:
SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
-----------------------------------------------
18-APR-09 10.37.31.765000 +01:00
Now we’ll change the data and commit the changes:
UPDATE staff SET full_name = 'Susan Brown' WHERE full_name = 'Susan Smith';
DELETE FROM staff WHERE full_name = 'Stuart Haines';
UPDATE staff SET full_name = UPPER(full_name);
COMMIT;
SELECT * FROM staff;
FULL_NAME
--------------------
SUSAN BROWN
HARRY TOMLINSON
So we’ve changed and committed the data. Now let’s test my claim. I said that Flashback would let us see the data as it was at a previous point in time, and that it would be really easy to do. So let’s try to get a view of the data as it was a few minutes ago, before we made those three changes.
It’s as simple as this:
SELECT * FROM staff AS OF TIMESTAMP TO_TIMESTAMP('18-APR-2009 10:37:31AM', 'DD-MON-YYYY HH:MI:SSAM');
FULL_NAME
--------------------
Susan Smith
Harry Tomlinson
Stuart Haines
So, all we’ve done is added ‘AS OF TIMESTAMP…’ to our SELECT statement, then used the TO_TIMESTAMP function to specify the time we want to ‘flash back’ to – the timestamp we retrieved just before we made the changes.
Those of you who grew up on earlier versions of Oracle, like me, or with other RDBMSs, I think will be impressed. The simplicity in particular makes it a really useful addition – no set-up needed, it’s right out of the box.
This feature is primarily designed for ‘undo’ functionality, allowing you to check or roll back unintended transactions. But you can probably think of production scenarios where you’d want to use it. You can create views based on Flashback SQL. Performance may not be particularly geared towards that type of usage though – check the documentation for the version of Oracle you are using and speak to your DBA about whether it makes sense to use it.
This is just one small feature of Oracle’s Flashback technology, there are other things you can do in the various RDBMS versions since Oracle 9i. Instead of the TIMESTAMP option you can specify the System Change Number (SCN) – an internal number Oracle uses to keep track of changes. Instead of using the AS OF clause on each SQL statement you can temporarily flash back a table – or even a whole database – to a point in time for the rest of the session. You can drop a table, and then flash it back to before the drop.
Hopefully this post has given you a flavour of what’s possible and will maybe encourage you to go and research it more yourself.
About the poster
Dean Bullen is Impartica’s SQL course leader and has been working with SQL since 1998. He previously worked for Oracle and has been working with SQL Server since 2006.