Go back in time and look how something looked then – Isn’t this one of those things that is really cool?
In Oracle, the set of features that enable this capability are clumped under “Oracle Flashback Features”. Technically, they can be bucketed as:
- Flashback query – as the name means, retrieve data as of a point in time in the past
- Flashback version query – similar to “flashback query” but you could also see all the changes one or more rows went through between given times
- Flashback transaction query – similar to “flashback version query” but additionally at a transaction level
- Flashback table – restore an entire table to a point back in time
- Flashback drop – Undo “drop table”
- Flashback database – Get the entire database to a point back in time
If you are like me, your mind should be racing, thinking about the possibilities that this presents.
While a lot of the features are available out of the box, some features need some setup to grant appropriate permissions and to tweak settings related to “how much” of the above is possible. All of the above features are directly tied to UNDO and the farther back in time one wants to go, the more UNDO is required.
Flashback query with examples:
Today, we will look at “Flashback query”. As we saw, with flashback query, we could run a SELECT “as of” a point back in time, in one of two ways
- AS OF TIMESTAMP – Query “AS OF” a timestamp to see committed data
- AS OF SCN – Query “AS OF” a System Change Number to see committed data
Query a table “AS OF” a timestamp
SELECT * FROM EMPLOYEES_TABLE AS OF TIMESTAMP TO_TIMESTAMP('11-SEP-2013 01.55.00.000000');
…returns the data as of said time. One could even include a WHERE clause to limit the SQL further
Query a table “AS OF” a timestamp that is too far back in time
SELECT * FROM EMPLOYEES_TABLE AS OF TIMESTAMP TO_TIMESTAMP('01-JAN-2013 01.55.00.000000');
ORA-01555: snapshot too old: rollback segment number 1 with name “_SYSSMU1_3325614637$” too small
To correct, limit the timestamp to a value that is less than your “undo_retention” seconds init. parameter that can be obtained using the below in SQL*Plus
show parameter undo
Query a table “AS OF” a SCN
SELECT * FROM EMPLOYEES_TABLE AS OF SCN 8788132844576
You can obtain the current SCN using
SELECT current_scn, SYSTIMESTAMP FROM v$database;
Ticket to time-travel – Using DBMS_FLASHBACK with examples
This above was great but to exploit this feature one would rather not have to append “AS OF” to every query one has already written. It is akin to a human time-traveling back in time to China at the time the Great Wall was being built, but in today’s costume.
DBMS_FLASHBACK makes it unnecessary to say “AS OF” for every SQL and still be able to query as of a time, back in time. In other words DBMS_FLASHBACK even takes care of our costume, so to speak metaphorically. All an user needs is EXECUTE privilege on the package.
Travel back to a point in time
EXECUTE DBMS_FLASHBACK.ENABLE_AT_TIME('10-SEP-2013 01.55.00.000000');
…At this point, you are virtually back in time to ’10-SEP-2013 01.55.00.000000′
“Look Maa – No AS OF in the query!”
SELECT * FROM EMPLOYEES_TABLE
The query is returning results as of ’10-SEP-2013 01.55.00.000000′ at this point because we used DBMS_FLASHBACK above to go back in time!
Back to the “present”
You could change the time or disable flashback all-together to get back to present
Changes made to a table since a certain time
I can think of a good scenario to use this feature in – What would my code do had it run earlier this morning before developer “x” messed up the data?
There are numerous uses for the “Flashback Query” feature.
This query gives me the changes made to the table “EMPLOYEES_TABLE” since ‘10-SEP-2013 01.55.00.000000’
SELECT * FROM EMPLOYEES_TABLE MINUS SELECT * FROM EMPLOYEES_TABLE AS OF TIMESTAMP TO_TIMESTAMP('10-SEP-2013 01.55.00.000000');
This query above says
- Give me all the rows in table
- Take away the rows from (a) that looked exactly the same as of ‘10-SEP-2013 01.55.00.000000’
Although using timestamp is a lot simpler than using SCN, note that conversion from timestamp to the appropriate SCN internally can result in a 3-second granularity error since everything is dealt with using SCN internally.
How to do this in SQL Server?
Until SQL Server 2016, there was not quite an equivalent feature in SQL Server with experts recommending Database Snapshot and Change Data Capture. In 2016, although it is not quite equivalent under the covers, the Temporal Tables functionality is very similar to Oracle’s flashback.
10g – http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_fl.htm
11g – http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm
More – http://psoug.org/reference/flash_query.html