Oracle Database 10g: The Top 20 Features for DBAs
by Arup Nanda
Follow along! Download Oracle Database 10g
Week 1
Get a Movie, Not a Picture: Flashback Versions Query
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.
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 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 = '000A000C00000029';
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.
For more information about Flashback Versions Query, see the relevant section of the Oracle Database Concepts 10g Release 1 (10.1) guide.
Next Week: Rollback Monitoring