Oracle – Back To The Past – Using The Oracle Flashback Query Feature

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

  1. AS OF TIMESTAMP – Query “AS OF” a timestamp to see committed data
  2. 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');

…returns error:
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

EXECUTE DBMS_FLASHBACK.DISABLE;

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

  1. Give me all the rows in table
  2. 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.

References:

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s