Flashback version query and the proper use of timestamp and scn clauses

Flashback version query essentially enables you to lookup the incarnations of a row (defined by primary key) in the past, in a consecutive manner. Version information is depicted by a couple of pseudo-columns, namely versions_xid, versions_startscn, versions_endscn, versions_starttime, versions_endtime and versions_operation. See Using Oracle Flashback Version Query in the docs for explanations.

In combination with flashback query or flashback transaction query, one may restore a row incarnation from the past into a new table or even rollback to a past row incarnation within the same table.

This article will discuss flashback version query together with flashback query to restore one to many rows, just shown for a row of a unique key here for brevity, detailing when and when not to use timestamp and scn select where clauses to prevent pitfalls. An example table / dataset will be given, representing a real world scenario where some past data needs to be identified first and is then to be made available again.

Flashback version query uses the following pattern, including the pseudo-columns introduced above, on an actual application-, but not a system-table (alike flashback transaction query). A timestamp– or scn-range must be supplied to define the lookup window (defined by the stock of the available undo-data, remember) and to actually populate the pseudo-columns, respectively:

SELECT pseudo_col1, ..., app_col1, ... FROM app_tab
  { SCN | TIMESTAMP } { expr | MINVALUE }
  AND { expr | MAXVALUE } ]

Flashback query then provides for the historic data selection in terms of application-table columns, typically used to run a create table as select..., if you like, supplying an as of timestamp– or scn selector:

-- CTAS, maybe
SELECT app_col1, ... FROM app_tab
  AS OF { SCN | TIMESTAMP } expr

All theory so far, yet the real example upfront, make shure have grasped what this trinity in fact means:

  • Flashback version query
  • Flashback query
  • Flashback transaction query

Also be prepared to unhesitantly get down to pen and paper when it comes to history lookup time windows and row level incarnations of data. It’s quite tricky and bewildering sometimes, be concentrated, have a sketch of timelines at hand (you know for https://en.wikipedia.org/wiki/Primer_(film), for example, some visualization like https://en.wikipedia.org/wiki/File:Time_Travel_Method-2.svg may come in very handy).



Getting the utc and unix time in milliseconds from oracle

It is quite necessary these days, especially in the globalization/24*7-worlds to pass around (unix-)timestamps in a form being defined as:

The difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC.

Indeed there is a dedicated method for this in the Java editions since long being declared as public static long currentTimeMillis() of class System. Also, Web-environments based on Javascript and the like offer native logic to transform a given UTC-timestamp to the some clients timezone and timestamp, respectively.

Delivering that msecs-utc-value from Oracle may seem straightforward at first, but may become more and more a jungle of options and proposed possibilities of conversion, extraction and so on. It is especially puzzling iff one actually has no timestamp with time zone instance at hand and needs to start off from existent date or timestamp w/o time zone column data. That is, data having had no time zone stored upon inserting or updating, such that calculating back to UTC is impossible without an edicated guess.

Hence, the only option for original date or timestamp w/o time zone data is using the Oracle from_tz function (save yourself from checking out new_time, it supports a very limited number of time zones only), passing in a timestamp w/o time zone and a time zone region known to Oracle. Do not just pass in a derived time zone value, e.g. sessiontimestamp, because Oracle takes this for granted without actually looking back in time, regarding daylight saving and things. For example, in 1978 daylight saving has not yet been introduced in Germany but in 1988 it was in use:

-- what's in sessiontimezone currently ?
select sessiontimezone from dual;

-- that's ok, an educated guess on the original time zone
-- actually the only option you have
select from_tz(cast(to_date('01.08.1978 12', 'dd.mm.yyyy hh24') as timestamp),
    'Europe/Berlin') as xyz1,
  from_tz(cast(to_date('01.08.1988 12', 'dd.mm.yyyy hh24') as timestamp),
    'Europe/Berlin') as xyz1
from dual;
01/08/1978 12:00:00,000000 +01:00	01/08/1988 12:00:00,000000 +02:00

-- possible and likely given in the oracle docs but wrong, oracle just follows
--   your specification without actually looking back in time
-- iff you do this in winter, the result may become wrong again in another
--   way because sessiontimezone will now read +01:00
select from_tz(cast(to_date('01.08.1978 12', 'dd.mm.yyyy hh24') as timestamp),
    sessiontimezone) as xyz1,
  from_tz(cast(to_date('01.08.1988 12', 'dd.mm.yyyy hh24') as timestamp),
    sessiontimezone) as xyz1
from dual;
01/08/1978 12:00:00,000000 +02:00	01/08/1988 12:00:00,000000 +02:00

Once you have derived this time zone aware value, retieving the UTC time is just employing a supplied function and a cast like this:

select cast(sys_extract_utc(
  from_tz(cast(to_date('01.08.1978 12', 'dd.mm.yyyy hh24') as timestamp),
    'Europe/Berlin')) as date) as xyz1
from dual;

Finally, substract any time being passed before january 1st 1970 and calculate the msecs-value, preassuming that one Oracle day is, in numbers, exactly = 1.

select to_number(cast(sys_extract_utc(
  from_tz(cast(to_date('01.08.1978 12', 'dd.mm.yyyy hh24') as timestamp),
    'Europe/Berlin')) as date) - to_date('01.01.1970','dd.mm.yyyy'))
    * (24 * 60 * 60 * 1000) as xyz1
from dual;

have fun.