| Arup
Nanda is the author of
Oracle Privacy Security Auditing by
Rampant TechPress.
Get a Movie, Not a Picture:
Flashback Versions Query
Arup Nanda
OTN
Immediately
identify all the changes to a row, with zero
setup required
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. For example, for a
transaction that occurs at 10:12AM but is
effective as of 9:12AM, the applicable rate
is that at 9:12AM, not now.
Up until now, the
only option was to create a rate history
table to store the rate changes, and then
query that table to see if a history is
available. Another option was to record the
start and end times of the applicability of
the particular exchange rate in the RATES
table itself. When the change occurred, the
END_TIME column in the existing row was
updated to SYSDATE and a new row was
inserted with the new rate with the END_TIME
as NULL.
In Oracle Database 10g,
however, the Flashback Versions Query
feature obviates 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.
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 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
/
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V RATE
---------------------- ---------------------- ---------------- - ----------
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
Final Analysis
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. |
