29 | 03 | 2024
Latest Articles
Popular Articles

Administration

Flashback Versions Query and Flashback Transaction Query

User Rating:  / 1
PoorBest 

Flashback Versions Query and Flashback Transaction Query

 

Flashback Versions Query.

 

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
versions between scn minvalue and maxvalue
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



Flashback Transaction Query.



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.