Administration
Flashback Query
User Rating: / 0
- Details
-
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: 4388
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
2 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
2 dbms_flashback.disable;
3 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
2 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