| In
Oracle9i Database, we
saw the introduction of the
"time machine" manifested in the
form of Flashback Query. The
feature allows the DBA to see
the value of a column as of a
specific time, as long as the
before-image copy of the block
is available in the undo
segment. However, Flashback
Query only provides a fixed
snapshot of the data as of a
time, not a running
representation of changed data
between two time points. Some
applications, such as those
involving the management of
foreign currency, may need to
see the value data changes in a
period, not just at two points
of time. Thanks to the Flashback
Versions Query feature,
Oracle Database 10g
can perform that task easily and
efficiently.
Querying Changes to a Table
In this
example, I have used a bank's
foreign currency management
application. The database has a
table called RATES to record
exchange rate on specific times.
SQL> desc rates
Name Null? Type
----------------- -------- ------------
CURRENCY VARCHAR2(4)
RATE NUMBER(15,10)
This
table shows the exchange rate of
US$ against various other
currencies as shown in the
CURRENCY column. In the
financial services industry,
exchange rates are not merely
updated when changed; rather,
they are recorded in a history.
This approach is required
because bank transactions can
occur as applicable to a "past
time," to accommodate the loss
in time because of
remittances...
In
Oracle Database 10g,
however, the Flashback Versions
Query feature may obviate the
need to maintain a history table
or store start and end times.
Rather, using this feature, you
can get the value of a row as of
a specific time in the past with
no additional setup. Bear in
mind, however, that it depends
on the availability of the undo
information in the database, so
if the undo information has been
aged out, this approach will
fail.
For
example, say that the DBA, in
the course of normal business,
updates the rate several
times—or even deletes a row and
reinserts it:
insert into rates values ('EURO',1.1012);
commit;
update rates set rate = 1.1014;
commit;
update rates set rate = 1.1013;
commit;
delete rates;
commit;
insert into rates values ('EURO',1.1016);
commit;
update rates set rate = 1.1011;
commit;
After
this set of activities, the DBA
would get the current committed
value of RATE column by
SQL> select * from rates;
CURR RATE
---- ----------
EURO 1.1011
This
output shows the current value
of the RATE, not all the changes
that have occurred since the
first time the row was created.
Thus using Flashback Query, you
can find out the value at a
given point in time; but we are
more interested in building an
audit trail of the
changes—somewhat like recording
changes through a camcorder, not
just as a series of snapshots
taken at a certain point.
The
following query shows the
changes made to the table:
select versions_starttime, versions_endtime, versions_xid,
versions_operation, rate
from rates versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME
/
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE
---------------------- ---------------------- ---------------- - ----------
01-DEC-03 03.57.12 PM 01-DEC-03 03.57.30 PM 0002002800000C61 I 1.1012
01-DEC-03 03.57.30 PM 01-DEC-03 03.57.39 PM 000A000A00000029 U 1.1014
01-DEC-03 03.57.39 PM 01-DEC-03 03.57.55 PM 000A000B00000029 U 1.1013
01-DEC-03 03.57.55 PM 000A000C00000029 D 1.1013
01-DEC-03 03.58.07 PM 01-DEC-03 03.58.17 PM 000A000D00000029 I 1.1016
01-DEC-03 03.58.17 PM 000A000E00000029 U 1.1011
Note that
all the changes to the row are
shown here, even when the row
was deleted and reinserted. The
VERSION_OPERATION column shows
what operation
(Insert/Update/Delete) was
performed on the row. This was
done without any need of a
history table or additional
columns.
In the
above query, the columns
versions_starttime,
versions_endtime, versions_xid,
versions_operation are
pseudo-columns, similar to other
familiar ones such as ROWNUM,
LEVEL. Other pseudo-columns—such
as VERSIONS_STARTSCN and
VERSIONS_ENDSCN—show the System
Change Numbers at that time. The
column versions_xid shows the
identifier of the transaction
that changed the row. More
details about the transaction
can be found from the view
FLASHBACK_TRANSACTION_QUERY,
where the column XID shows the
transaction id. For instance,
using the VERSIONS_XID value
000A000D00000029 from above, the
UNDO_SQL value shows the actual
statement.
SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '000A000D00000029';
UNDO_SQL
----------------------------------------------------------------------------
insert into "ANANDA"."RATES"("CURRENCY","RATE") values ('EURO','1.1013');
In
addition to the actual
statement, this view also shows
the timestamp and SCN of commit
and the SCN and timestamp at the
start of the query, among other
information.
Finding
Out Changes During a Flashback
Period
Now,
let's see how we can use the
information effectively. Suppose
we want to find out the value of
the RATE column at 3:57:54 PM.
We can issue:
select rate, versions_starttime, versions_endtime
from rates versions
between timestamp
to_date('12/1/2003 15:57:54','mm/dd/yyyy hh24:mi:ss')
and to_date('12/1/2003 16:57:55','mm/dd/yyyy hh24:mi:ss')
/
RATE VERSIONS_STARTTIME VERSIONS_ENDTIME
---------- ---------------------- ----------------------
1.1011
This
query is similar to the
flashback queries. In the above
example, the start and end times
are null, indicating that the
rate did not change during the
time period; rather, it includes
a time period. You could also
use the SCN to find the value of
a version in the past. The SCN
numbers can be obtained from the
pseudo-columns VERSIONS_STARTSCN
and VERSIONS_ENDSCN. Here is an
example:
select rate, versions_starttime, versions_endtime
from rates versions
between scn 1000 and 1001
/
Using the
keywords MINVALUE and MAXVALUE,
all the changes that are
available from the undo segments
is displayed. You can even give
a specific date or SCN value as
one of the end points of the
ranges and the other as the
literal MAXVALUE or MINVALUE.
For instance, here is a query
that tells us the changes from
3:57:52 PM only; not the
complete range:
select versions_starttime, versions_endtime, versions_xid,
versions_operation, rate
from rates versions between timestamp
to_date('12/11/2003 15:57:52', 'mm/dd/yyyy hh24:mi:ss')
and maxvalue
order by VERSIONS_STARTTIME
/
Final
Analysis of Flashback
Flashback Versions Query
replicates the short-term
volatile value auditing of table
changes out of the box. This
advantage enables the DBA to get
not a specific value in the
past, but all the changes in
between, going as far bask as
the available data in undo
segments. Therefore, the maximum
available versions are dependent
on the UNDO_RETENTION parameter.
|
