​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
Flashback Queries


Flashback has been available since Oracle9 and is one of those features that although you may not use it a lot, you’ll be glad that it’s there when you do! Put simply, flashback  allow you to go back in time - as it were - and see what was contained in tables at that time. Usefully you can also use it to “un-drop”  dropped tables

 

There are a few flavours of flashback including:-


Flashback queries  

 

Retrieve data from a table at a time in the past that you specify


Flashback Version Query


Retrieve metadata and historical data for a specific time interval 


Flashback Transaction Query


Use this feature to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval


DBMS_FLASHBACK Package

Use this feature to set the internal Oracle Database clock to a time in the past so that you can examine data that was current at that time, or to roll back a transaction and its dependent transactions while the database remains online

 

For data recovery the following operations are available too:-


Oracle Flashback Table


Use this feature to restore a table to its state at a previous point in time. You can restore a table while the database is on line, undoing changes to only the specified table.


Oracle Flashback Drop


Use this feature to recover a dropped table. This feature reverses the effects of a DROP TABLE statement.

 

 

Oracle Flashback Database

Use this feature to quickly return the database to an earlier point in time, by undoing all of the changes that have taken place since then. This is fast, because you do not have to restore database backups.

 

Of the above I’ll just discuss the ones that I have actually used.

 

There are a number of pre-requisites that must be configured on your database before using flashback queries.  Generally this means ensuring your database uses automatic UNDO management, enabling supplemental logging and the granting of a few privileges.  Consult the documentation for a full list.

 

The best way to get an idea of how useful flashback queries are is to see some examples.

 

Flashback query

I use this type of query all the time. Very useful if you’ve deleted some rows by mistake.

e.g What was in the table 1 hour ago that’s not in it now?

 

Select * from mytable as of Timestamp (systimestamp – interval ’60’ MINUTE)

Minus

Select * from mytable

 

And if you wanted to put those rows back into the table, simply

 

Insert into mytable

Select * from mytable as of Timestamp (systimestamp – interval ’60’ MINUTE)

Minus

Select * from mytable

 

 

Flashback version query

 

SQL> create table tom(x number);

 Table created.

 

SQL> insert into tom values(99);

 1 row created.

 SQL> commit;

 Commit complete.

 

SQL> insert into tom values(24);

1 row created.

SQL> commit;

 Commit complete.

 

SQL> delete from tom where x = 24;

 1 row deleted.

 SQL> commit;

 Commit complete.

 

SQL> insert into tom values(99);

 1 row created.

 SQL> commit;

 Commit complete.

 

SQL> update tom set x = 100 where x = 99;

 2 rows updated.

 SQL> commit;

 Commit complete.

 

SQL> select versions_xid XID, versions_startscn start_scn,

  2  versions_endscn end_scn,versions_operation operation

  3  from tom

  4  versions between scn minvalue and maxvalue

  5  /

 

XID                                       START_SCN             END_SCN        Oper

04002F00AE740500               65929311279                                    U

04002F00AE740500               65929311279                                    U

05001C003E7A0500               65929311273         65929311279       I

0C0028009CA40200               65929311268                                    D

0C000700F1A40200               65929311261         65929311268       I

0C00160086A40200               65929311255         65929311279       I

 

6 rows selected.

SQL>

 

Now that we have some transaction ID’s from the above query we can drill down to see who made particular changes to a table. NB the SQL shown in the output is what is required to UNDO the operation

 

Select  xid,start_scn,commit_scn,operation,logon_user,undo_sql

From flashback_transaction_query

Where xid = hextorwa(‘04002F00AE740500’)

 

 XID               START_SCN COMMIT_SCN OPERATION

LOGON_USER

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

UNDO_SQL

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

04002F00AE740500 6.5929E+10 6.5929E+10 UPDATE

CAPEL

update "CAPEL"."TOM" set "X" = '99' where ROWID = 'AABqjXABmAABxjyAAA';

 

04002F00AE740500 6.5929E+10 6.5929E+10 UPDATE

CAPEL

update "CAPEL"."TOM" set "X" = '99' where ROWID = 'AABqjXABmAABxjyAAC';

 

XID               START_SCN COMMIT_SCN OPERATION

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

LOGON_USER

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

UNDO_SQL

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

 

04002F00AE740500 6.5929E+10 6.5929E+10 BEGIN

CAPEL

 

See ALL changes since a specific time period

 

Select xid ,start_scn,commit_scn,operation,table_name,table_owner

From flashback_transaction_query

Where table_name = 'TOM'

And start_timestamp > (systimestamp - interval '60' minute)

 

You should note that the above query is slowwwwwwwwwwwww!

 

 

 

 

 

Recovering a deleted table

Finally a word about Oracle flashback DROP which can be a real life-saver. You can use this to “undrop” a table. First of all run the following command from sqlplus

 SQL> show recyclebin

 ORIG NAME       RECYCLEBIN NAME                       OBJECT TYPE  DROP TIME

=================================================

TOM                   BIN$FtuCRSEtdXrgwjEe+QDWEg==$0    TABLE        2012-05-16:11:00:03

TOM                  BIN$BOS9WsVp+xrgwjEe+QCDKQ==$0   TABLE        2012-04-23:14:20:46

TOM                  BIN$69BIyZpUyQXgshR9ZQAbmA==$0    TABLE        2012-03-22:16:21:22

TOM1                BIN$ZYroNAuQs5vgwv7gNoBQPw==$0     TABLE        2012-08-24:14:12:06

TOM2                BIN$9M9reWkwnc7g0lBMK4Cfhw==$0      TABLE        2014-01-14:16:37:46

TOM_RAT          BIN$jBYW55tyxQTg0uhxmwBIkw==$0     TABLE        2013-09-03:10:09:07

TXTZ_TOM1       BIN$M/uNtt1i2IXgwjEe+QCIUg==$0      TABLE        2012-06-22:12:32:58

 

From here you can select out the contents of the desired dropped table - viz

 

Select * from “BIN$FtuCRSEtdXrgwjEe+QDWEg==$0”;

 

Or more useful  “undrop” it thus

FLASHBACK TABLE " BIN$FtuCRSEtdXrgwjEe+QDWEg==$0" TO BEFORE DROP;

 

NB Unless you do a flashback of the whole database you still cannot (at least in Oracle V10) undo a TRUNCATE table command