I initially tested this with 11.2.0.2.0 and ran into
Bug 10358019 Queries against FLASHBACK_TRANSACTION_QUERY return wrong results
Solution was to apply 11.2.0.2.3
Note that minimal suuplemental log data must be added for the database in order to use this feature
SQL> connect / as sysdba
Connected.
SQL> select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
SUPPLEME SUP SUP SUP SUP
-------- --- --- --- ---
YES YES NO NO NO
If the first column wouldn' t have been YES then one add gather supplemental log data with
SQL> alter database add supplemental log data;
Database altered.
In order to grant someone access to the view flashback_transaction_query
SQL> grant select any transaction to test;
Grant succeeded.
SQL> grant execute on dbms_flashback to test;
Grant succeeded.
SQL> connect test/test
Connected.
SQL> create table T_CURRENCY (CUR_CODE varchar2(1 CHAR),CUR_DESCRIPTION varchar2(30));
Table created.
SQL> alter table T_CURRENCY add constraint T_CURRENCY_PK primary key (CUR_CODE);
Table altered.
SQL> insert into T_CURRENCY values ('€','Euro');
1 row created.
SQL> insert into T_CURRENCY values ('$','American Dollar');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_currency;
CUR_ CUR_DESCRIPTION
---- ------------------------------
€ Euro
$ American Dollar
SQL> create table T_CUR_RATE (CURRENCY_1 varchar2(1 CHAR),CURRENCY_2 varchar2(1CHAR),EXCH_RATE number,EXCH_TIME timestamp default systimestamp) ROWDEPENDENCIES;
Table created.
SQL> alter table t_cur_rate add constraint t_cur_rate_pk primary key (currency_1,currency_2);
Table altered.
SQL> alter table T_CUR_RATE add constraint CUR_CODE_FK1 foreign key (CURRENCY_1) references T_CURRENCY (CUR_CODE);
Table altered.
SQL> alter table T_CUR_RATE add constraint CUR_CODE_FK2 foreign key (CURRENCY_2) references T_CURRENCY (CUR_CODE);
Table altered.
SQL> insert into T_cur_rate (currency_1,currency_2,exch_rate) values ('€','$',1.3037);
1 row created.
SQL> commit;
SQL> update t_cur_rate set exch_rate=1.4155,exch_time=systimestamp where currency_1='€' and currency_2='$';
1 row updated.
SQL> commit;
Commit complete.
SQL> update t_cur_rate set exch_rate=1.4197 ,exch_time=systimestamp where currency_1='€' and currency_2='$';
1 row updated.
SQL> commit;
Commit complete.
SQL> select versions_xid as XID,
2 versions_startscn as startscn,
3 versions_endscn as endscn,
4 versions_operation as Operation,
5 currency_1, currency_2, exch_rate, exch_time
6 from t_cur_rate
7 versions between scn minvalue and maxvalue
8 as of scn 10976691
9 where currency_1='€' and currency_2='$';
XID STARTSCN ENDSCN O CURR CURR EXCH_RATE
---------------- ---------- ---------- - ---- ---- ----------
EXCH_TIME
---------------------------------------------------------------------------
07001900D70D0000 10976497 U € $ 1.4197
19-JUL-11 04.14.44.597288 PM
07001100D90D0000 10975898 10976497 U € $ 1.4155
19-JUL-11 04.09.57.250073 PM
06001D0058120000 10975770 10975898 I € $ 1.4105
19-JUL-11 04.07.04.093513 PM
SQL> column exch_time format a30
SQL> select a.*,ora_rowscn from t_cur_rate a;
CURR CURR EXCH_RATE EXCH_TIME ORA_ROWSCN
---- ---- ---------- ------------------------------ ----------
€ $ 1.4197 19-JUL-11 04.14.44.597288 PM 10976497
Query flashback_transaction_query and retrieve and apply undo sql.
SQL> select xid,undo_sql,table_name from flashback_transaction_query where table_owner='TEST' and table_name='T_CUR_RATE';
XID
----------------
UNDO_SQL
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
07001900D70D0000
update "TEST"."T_CUR_RATE" set "EXCH_RATE" = '1.4155', "EXCH_TIME" = TO_TIMESTAM
P('19-JUL-11 04.09.57.250073 PM') where ROWID = 'AAARuyAAEAAA24HAAA';
T_CUR_RATE
07001100D90D0000
update "TEST"."T_CUR_RATE" set "EXCH_RATE" = '1.4105', "EXCH_TIME" = TO_TIMESTAM
P('19-JUL-11 04.07.04.093513 PM') where ROWID = 'AAARuyAAEAAA24HAAA';
T_CUR_RATE
To speed up queries we can use the hextoraw(xid) operation.
SQL> select xid,undo_sql,table_name from flashback_transaction_query where xid=hextoraw('07001900D70D0000');
XID
----------------
UNDO_SQL
--------------------------------------------------------------------------------
TABLE_NAME
--------------------------------------------------------------------------------
07001900D70D0000
update "TEST"."T_CUR_RATE" set "EXCH_RATE" = '1.4155', "EXCH_TIME" = TO_TIMESTAM
P('19-JUL-11 04.09.57.250073 PM') where ROWID = 'AAARuyAAEAAA24HAAA';
T_CUR_RATE
07001900D70D0000
SQL> update "TEST"."T_CUR_RATE" set "EXCH_RATE" = '1.4155', "EXCH_TIME" = TO_TIMESTAMP('19-JUL-11 04.09.57.250073 PM') where ROWID = 'AAARuyAAEAAA24HAAA';
1 row updated.
SQL> commit;
Commit complete.