Featured Database Articles

Oracle's Flashback Query - TIMESTAMP or SCN?

Oracles
Flashback Query(SQL-driven) makes use of both TIMESTAMP and SCN--but which
should you use? It just may be a matter of preference but requires some
thoughtful considerations.

In order to take advantage
of Oracles flashback query feature, for SQL-driven flashback, the SELECT
statement makes use of the AS OF or VERSIONS BETWEEN clause to retrieve data
from the past from tables, views, or materialized views.

Both the VERSIONS BETWEEN
and the AS OF clause requires the user to code in the SQL an SCN or TIMESTAMP
for proper retrieval of past information. The question often becomes should I
use an SCN or should I use a TIMESTAMP? The answer is not always clear but Ill
investigate both here to help you grapple with your next decision.

First, it makes complete
sense to define what a TIMESTAMP and an SCN is:

TIMESTAMP is an extension of the DATE datatype, which stores
all the information that the DATE datatype stores (month, day, year, hour,
minute, second) but also includes fractional seconds. Personally, I find this a
bit confusing since date, by the strictest definition, is JUST a particular
month, day, and year when an event happened. Regardless, since TIMESTAMP is the
datatype required for a SQL-driven the flashback query, the following SQL
conversions could come in handy:

Just as there is a call to
SYSDATE to get the current system date and time, there is a call to get the
current system TIMESTAMP.

SCN (System Change Number) is
nothing more than a stamp (number) that defines a committed version of a
database at a point in time. This means that every committed transaction is
assigned a unique SCN. Worthy to note, Oracle will also perform internal work
that generates SCNs. I think TIMESTAMP has probably overtaken the use of SCN
mostly because many just don't know where to extract a valid SCN from. Here are
a few areas that could help you in this endeavor.

Just remember that there are
literally 100's of tables and views within Oracle that maintain some form of an
SCN. Use the following SQL statement to find which ones you might want to use
for your next flashback query.

Use of an SCN in SQL-driven
flashback queries isnt much different than using a TIMESTAMP. Just plug in
your SCN number.

SELECT *
FROM flash_test
AS OF SCN 2249100;
SELECT *
FROM flash_test
VERSIONS BETWEEN SCN 2249600 AND 2249659;

You can also play around with
converting from a TIMESTAMP to an SCN and an SCN to a TIMESTAMP. These come in
handy if you want to standardize on a particular method or help you in
pinpointing which is better to use during recovery scenarios.

Determining to use an SCN or
TIMESTAMP for flashback queries seems to be a matter of comfort. I think it
really comes down to whether youre comfortable working with times or numbers.
Worthy to note, since there are various human factors that come into play when
discussing time (TIMEZONE and client-side wall clock or system time to name
just a couple) it may be better to always convert to a real database server side
TIMESTAMP and then an SCN. Moreover, this sounds much more professional, can
help obfuscate the issue, and make you sound like youre remaining technical;
always a plus!