Administration

Flashback Query

User Rating:  / 0
PoorBest 
Parent Category: Articles
Created on Sunday, 19 September 2010 22:52
Last Updated on Friday, 24 August 2012 16:56
Published on Sunday, 19 September 2010 22:52
Written by Guy Lambregts
Hits: 4099

Flashback Query.


Query the database as it was in the (recent) past.

Flashback Query was introduced in Oracle 9i and was further enhanced in Oracle 10G. Flashback Query depends on undo data.

SQL> alter system set undo_retention=7200;

System altered.

 

Our undo tablespace has the attribute undo guarantee. Guaranteed undo, even though not a requirement,  can be useful with respect to flashback queries.

 

SQL> select tablespace_name,retention from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME           RETENTION

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

UNDOTBS1                          GUARANTEE

 

Let us set up a test scenario

 

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> column exch_time format a30;
SQL> select a.*,ora_rowscn from t_cur_rate a;

CURR CURR  EXCH_RATE EXCH_TIME                      ORA_ROWSCN
---- ---- ---------- ------------------------------ ----------
€    $        1.3037 19-SEP-10 08.50.55.452862 PM      9147091

 

However the exchange rate varies during the whole day. The below SQL is executed.



SQL> update t_cur_rate set exch_rate=1.3001,exch_time=systimestamp where currency_1='€' and  currency_2='$';

1 row updated.

SQL> commit;

Commit complete.


SQL> select a.*,ora_rowscn from t_cur_rate a;

CURR CURR  EXCH_RATE EXCH_TIME                      ORA_ROWSCN
---- ---- ---------- ------------------------------ ----------
€    $        1.3001 19-SEP-10 10.10.40.918533 PM      9161502


SQL> update t_cur_rate set exch_rate=1.2989,exch_time=systimestamp where currency_1='€' and currency_2='$';


SQL> select a.*,ora_rowscn from t_cur_rate a;

CURR CURR  EXCH_RATE EXCH_TIME                      ORA_ROWSCN
---- ---- ---------- ------------------------------ ----------
€    $        1.2989 19-SEP-10 10.18.53.140120 PM      9162115


1 row updated.

SQL> commit;

Commit complete.

 

But now we would like to know what the exchange rate was at a given time, more precisely we would like to know what the exchange rate was at 10 PM CET




SQL> select exch_rate from t_cur_rate as of timestamp to_timestamp('19-SEP-10 10.00.00.000000 PM');

EXCH_RATE
----------
1.3001



Since internally Oracle uses the system change numbers we can use the in stead of the AS OF TIMESTAMP  the AS OF SCN clause.



SQL> select exch_rate,ora_rowscn from t_cur_rate as of SCN 9162100;

EXCH_RATE  ORA_ROWSCN
---------- ----------
1.3001     9161502

 

Note that one can also use the dbms_flashback procedure to query the database as it was in a previous point in time.



SQL> grant execute on dbms_flashback to PM;

Grant succeeded.



What is the actual system change number ?.


SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
9163019

 

Let us enable flashback queries for the session.
This is interesting when flasback queries for multiple queries are to be used.
It is so because the sql statement itself does not change. ( think about pl/sql code )


SQL> begin
dbms_flashback.enable_at_time('19-SEP-10 10.00.00.000000 PM');
3  end;
4  /

PL/SQL procedure successfully completed.


SQL> select exch_rate from t_cur_rate;

EXCH_RATE
----------
1.3001

SQL> begin
2  dbms_flashback.disable;
3  end;
4  /

PL/SQL procedure successfully completed.

SQL> select exch_rate from t_cur_rate;

EXCH_RATE
----------
1.2989


SQL> begin  
dbms_flashback.disable;
dbms_flashback.enable_at_system_change_number(9162000);
4  end;
5  /

PL/SQL procedure successfully completed.

SQL> select exch_rate,ora_rowscn from t_cur_rate;

EXCH_RATE  ORA_ROWSCN
---------- ----------
1.3001     9161502


SQL> begin
dbms_flashback.disable;
3  end;
4  /

PL/SQL procedure successfully completed.

SQL> select exch_rate,ora_rowscn from t_cur_rate;

EXCH_RATE  ORA_ROWSCN
---------- ----------
1.2989     9162115