[Oracle] Retrieve data with Marty McFly

Published on 15 April 2014

You misclicked and deleted a « few » rows that you shouldn’t have? You compiled a package, overwriting the previous code, but you didn’t want that? Don’t worry Oracle has its own built-in DeLorean : flashback queries.Flashback queries, is a feature that allows you to do query past data, i.e. as it was at a certain given date. Needless to say it’s quite powerful and really usefull.

 

flashback_query_comic_strip

How to use it

The usage is quite simple, you only need to specify the date for which you want to query your data, using the keys words « AS OF »:

SQL> create table mytab (id number, value varchar(100));
Table created

SQL> insert into mytab (id,value) values (1,'old_value_1');
1 row inserted

SQL> insert into mytab (id,value) values (2,'old_value_2');
1 row inserted

SQL> insert into mytab (id,value) values (3,'old_value_3');
1 row inserted

SQL> select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') DT from dual;
DT

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

15/04/2014 17:39:47

SQL> update mytab set value = 'new_value_1' where id = 1;

1 row updated

SQL> select to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') DT from dual;

DT

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

15/04/2014 17:40:02

SQL> select * from mytab;

        ID VALUE

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

         1 new_value_1

         2 old_value_2

         3 old_value_3

SQL> select * from mytab as of timestamp to_timestamp('15/04/2014 17:39:47','DD/MM/YYYY HH24:MI:SS');

        ID VALUE

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

         1 old_value_1

         2 old_value_2

         3 old_value_3
         

Pretty simple. You can only do flashbacks query with TIMESTAMPS and SCN (see previous post for SCN infos) in the where clause.

Since its a simple select query you can do for examples:

  • Create a backup of your data as it was yesterday:
Create mytab_bak as select * from my_tab as of timestamp trunc(sysdate) -1;
  • Retrieve an older version of a package: (reserved to SYSDBA profiles, sadly)
select * from sys.user_source as of timestamp sysdate-1
where name = 'MY_PACKAGE'
and type = 'PACKAGE BODY';

Limitations

To use flashback queries you need to (as always) :

  • Have sufficient rights,
grant FLASHBACK on mytab to myuser;
  • A compatible Oracle version, that would be 10g+ ,
  • Flashback queries are enabled on your DB, this configuration is to be done with DBA rights (undo management).

Also note that since flashback queries are using the undo tablespace, you can only do queries on data in a given period of time depending on UNDO_RETENTION parameter. Retention time is usually a week or so.

 

comments powered by Disqus