Database Reference
In-Depth Information
Flashback
In the past, Oracle always decided the point in time at which our queries would be consistent. That is, Oracle made it
such that any resultset we opened would be current with respect to one of two points in time:
•
The point in time the query was opened.
This is the default behavior in
READ COMMITTED
isolation (we'll be covering the differences between
READ COMMITTED
,
READ ONLY
, and
SERIALIZABLE
transaction levels in Chapter 7).
•
The point in time the transaction that the query is part of began.
This is the default behavior in
READ ONLY
and
SERIALIZABLE
transaction levels.
Starting with Oracle 9
i
's
flashback query
feature, however, we can tell Oracle to execute a query “as of ”
(with certain reasonable limitations on the length of time you can go back into the past, of course). With this, you can
“see” read consistency and multiversioning even more directly.
■
the flashback data archive, used for long-term flashback queries (months or years into the past) and available
with oracle 11
g
release 1 and above, does not use read consistency and multiversioning to produce the version of data
that was in the database at some prior point in time. instead, it uses before-image copies of the records it has placed into
the archive. We'll come back to the flashback data archive in a later chapter. note also that the flashback data archive is
a
feature
of the database, starting with 11.2.0.4 and above. previously, it was a separately priced option to the database;
now it is a feature for all to use without additional license cost.
Note
Consider the following example. We start by getting an SCN (System Change or System Commit number; the
terms are interchangeable). This SCN is Oracle's internal clock: every time a commit occurs, this clock ticks upward
(increments). We could use a date or timestamp as well, but here the SCN is readily available and very precise:
SCOTT@ORA12CR1> variable scn number
SCOTT@ORA12CR1> exec :scn := dbms_flashback.get_system_change_number;
PL/SQL procedure successfully completed.
SCOTT@ORA12CR1> print scn
SCN
----------
13646156
■
the DBMS_flaShBaCK package might have restricted access on your system. i granted execute on this
package to SCott in my database; you may have to do the same.
Note
