In database system, some times flashback for the older versions of the data or query is important for some applications. In Oracle9i, there is a feature called "time machine" manifested a form of Flashback query. This feature allows DBA to see the value of the column as of a specific time, as long as the before-image copy is available in UNDO segment. However, this feature provides a fixed snapshot of the data as of a time, not a running representation of the changed data between two time points.


During thinking process, I came cross an application , which involves the management of foreign currency , may need to see the value of data changes in a period, not just at a specific time. In Oracle 10g and onwards, you can perform this task easily and efficiently.


For Example:


This table shows the exchange rate of US$ against various other currencies. In financial industry, exchange rates are not updated when changes rather they are recorded in history. Suppose, if we do transaction at 1:00PM but it is effective at 11:00AM, the applicable rate is that at 11:00 AM, not now.


SQL> DESC RATES;

Name                Null? Type

---------------------- -------- ------------------ 

CURRENCY             VARCHAR2(4)

RATE                      NUMBER(15,10)


Up until now, the only option was to create rate history table and store rate changes and query table to see the history. Alternative option is to create additional columns in RATES table  to track the changes using trigger.


In Oracle10g, the Flashback Versions Query feature eliminate the need to maintain history table or store start and end times.


These are the operations we performed on the RATES table:


SQL> insert into rates values('CAD',1.23183); 

1 row created. 

SQL> commit; 

Commit complete. 

SQL> update rates set rate=1.23184; 

1 row updated.

SQL> commit; 

Commit complete. 

SQL> update rates set rate=1.23000; 

1 row updated. 

SQL> commit;

Commit complete. 

SQL> delete rates; 

1 row deleted. 

SQL> commit; 

Commit complete. 

SQL> insert into rates values('CAD',1.23111); 

1 row created. 

SQL> commit; 

Commit complete. 

SQL> update rates set rate=1.23112; 

1 row updated. 

SQL> commit;

Commit complete.


The following query shows the changes made to the table:


SQL> select versions_starttime as v_starttime, versions_endtime as v_endtime,

versions_xid as v_xid, versions_operation as v, rate

from rates versions between timestamp minvalue and maxvalue

order by VERSIONS_STARTTIME ;


V_STARTTIME              V_ENDTIME                       V_XID                  V   RATE

-----------------------------        -----------------------------            -----------------------     ---  ---------------

26-JAN-09 11.58.37 AM   26-JAN-09 11.58.39 AM   0700190030030000    I  1.23183 

26-JAN-09 11.58.37 AM   26-JAN-09 11.58.52 AM   02000F0039030000   U  1.23184

26-JAN-09 11.58.52 AM   26-JAN-09 11.59.01 AM   0700110031030000   U  1.23

26-JAN-09 11.59.01 AM                                         09000B00F2030000  D  1.23

26-JAN-09 11.59.16 AM   26-JAN-09 11.59.33 AM   04000E0041030000   I  1.23111 

26-JAN-09 11.59.33 AM                                         100190041030000     U  1.23112


Note that for the table all the changes to the row are shown here when the row was delete and reinserted. The VERIONS_OPERATION column shows which DML operator was performed on the row. This was done without any need of the history tables or additional columns.


More details about the actual query can be found by querying FLASHBACK_TRANSACTION_QUERY table using XID or transaction id.


SQL > SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY

WHERE XID = '04000E0041030000';

UNDO_SQL

-----------------------------------------------------------------

insert into "DENISH"."RATES"("CURRENCY","RATE") values ('CAD','1.23111');



It is extremely important to remember that the maximum available versions of the query dependent on the UNDO_RETENTION parameter.